MESSAGE
| DATE | 2014-11-22 |
| FROM | Ruben Safir
|
| SUBJECT | Subject: [LIU Comp Sci] Oracle Files for the Homework and Oracle Resources
|
From owner-learn-outgoing-at-mrbrklyn.com Sat Nov 22 18:44:46 2014
Return-Path:
X-Original-To: archive-at-mrbrklyn.com
Delivered-To: archive-at-mrbrklyn.com
Received: by mrbrklyn.com (Postfix)
id 978D6161158; Sat, 22 Nov 2014 18:44:46 -0500 (EST)
Delivered-To: learn-outgoing-at-mrbrklyn.com
Received: by mrbrklyn.com (Postfix, from userid 28)
id 7DCA7161163; Sat, 22 Nov 2014 18:44:46 -0500 (EST)
Delivered-To: learn-at-nylxs.com
Received: from mailbackend.panix.com (mailbackend.panix.com [166.84.1.89])
by mrbrklyn.com (Postfix) with ESMTP id 70448161158
for ; Sat, 22 Nov 2014 18:44:45 -0500 (EST)
Received: from [10.0.0.42] (unknown [96.57.23.82])
by mailbackend.panix.com (Postfix) with ESMTP id 36EB315BCF
for ; Sat, 22 Nov 2014 18:44:45 -0500 (EST)
Message-ID: <5471202B.9090200-at-panix.com>
Date: Sat, 22 Nov 2014 18:45:47 -0500
From: Ruben Safir
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.1.0
MIME-Version: 1.0
To: learn-at-nylxs.com
Subject: [LIU Comp Sci] Oracle Files for the Homework and Oracle Resources
Content-Type: multipart/mixed;
boundary="------------000402010304020901050408"
Sender: owner-learn-at-mrbrklyn.com
Precedence: bulk
Reply-To: learn-at-mrbrklyn.com
This is a multi-part message in MIME format.
--------------000402010304020901050408
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
How do you see in Oracle the primary and foreign constraints? I am
attaching the Oracle files i used to create the fundmental databases as
they exist on this hangout titled Company Schema.
Everything was done in SQLPLUS because the APEX thingie is fundamentally
broken.
***Some notes on the Oracle Installation and creations on Linux***
Note - Oracle STUPID, REALLY STUPID. The httpd part of oracle and the
server will not work is your hostname and ipaddress is not CORRECT.
Fix that first. Some OS's ignore /etc/hostname (or even /etc/hosts -
thank you system fucking D)
First, the current rpm from oracle is at
http://www.nylxs.com/docs/grad_school/database/oracle-xe-11.2.0-1.0.x86_64.rpm.zip
In order to install on any pacman.arch based system one needs to rebuild
this and create a pacman package file
drop the rpm into the same directory as the pacman file
***make the oracle user with a home directory
This the pacman build file. It is missing the RPM so you need BOTH
http://www.nylxs.com/docs/grad_school/database/oracle-xe.tar.gz
untar it
tar -xzvf oracle-xe.tar.gz
move the the directory it creates
[ruben-at-laptop ~]$ cd oracle-xe
mv the rpm there
mv ../oracle-xe-11.2.0-1.0.x86_64.rpm.zip ./
[ruben-at-laptop oracle-xe]$ ls -al
total 847860
drwxr-xr-x 4 ruben users 4096 20.11.2014 13:50 ./
drwx------ 27 ruben users 4096 22.11.2014 21:00 ../
drwxr-xr-x 3 ruben users 4096 20.11.2014 13:50 pkg/
drwxr-xr-x 6 ruben users 4096 20.11.2014 13:49 src/
-rw-r--r-- 1 ruben users 438 14.04.2013 10:34 listener.ora
-rw-r--r-- 1 ruben users 156 14.04.2013 10:34 oracle_env.csh
-rw-r--r-- 1 ruben users 156 14.04.2013 10:34 oracle_env.sh
-rw-r--r-- 1 ruben users 2608 14.04.2013 11:44 oracle.install
-rw-r--r-- 1 ruben users 19759 14.04.2013 10:34 oracle-xe
-r--r--r-- 1 ruben users 315891481 20.11.2014 09:07
oracle-xe-11.2.0-1.0.x86_64.rpm.zip
-rw-r--r-- 1 ruben users 37 14.04.2013 10:34 oracle-xe.conf
-rw-r--r-- 1 ruben users 144 14.04.2013 10:38 oracle-xe.service
-rw-r--r-- 1 ruben users 2994 17.05.2014 05:19 PKGBUILD
[ruben-at-laptop oracle-xe] makepkg -s
Now you have the .xz file that pacman needs to make oracle and configure it
[ruben-at-laptop oracle-xe]$ ls -al
total 847860
drwxr-xr-x 4 ruben users 4096 20.11.2014 13:50 ./
drwx------ 27 ruben users 4096 22.11.2014 21:00 ../
drwxr-xr-x 3 ruben users 4096 20.11.2014 13:50 pkg/
drwxr-xr-x 6 ruben users 4096 20.11.2014 13:49 src/
-rw-r--r-- 1 ruben users 438 14.04.2013 10:34 listener.ora
-rw-r--r-- 1 ruben users 156 14.04.2013 10:34 oracle_env.csh
-rw-r--r-- 1 ruben users 156 14.04.2013 10:34 oracle_env.sh
-rw-r--r-- 1 ruben users 2608 14.04.2013 11:44 oracle.install
-rw-r--r-- 1 ruben users 19759 14.04.2013 10:34 oracle-xe
-rw-r--r-- 1 ruben users 276123404 20.11.2014 13:59
oracle-xe-11.2.0_1.0-2-x86_64.pkg.tar.xz <====LOOOOK
-r--r--r-- 1 ruben users 315891481 20.11.2014 09:07
oracle-xe-11.2.0-1.0.x86_64.rpm.zip
-rw-r--r-- 1 ruben users 37 14.04.2013 10:34 oracle-xe.conf
-rw-r--r-- 1 ruben users 144 14.04.2013 10:38 oracle-xe.service
-rw-r--r-- 1 ruben users 2994 17.05.2014 05:19 PKGBUILD
[ruben-at-laptop oracle-xe]$ sudo pacman -U
oracle-xe-11.2.0_1.0-2-x86_64.pkg.tar.xz
Run the '/etc/rc.d/oracle-xe configure' as root, REMEMBER TO CHOOSE THE
PASSWORD FOR SYS AND SYSTEM CONTAINS
change you groups and password files. make sure you and oracle is in
the /etc/groups
[ruben-at-laptop oracle-xe]$ cat /etc/group
...
colord:x:124:
docker:x:142:
oinstall:x:1002:
dba:x:1003:oracle,ruben
[ruben-at-laptop oracle-xe]$
Important Oracle SQL Cheat Sheet:
* define_editor='vi'
Better Add it to a oracle start script
* make vi your permanent editor you have to write "define _editor='vi'"
into the file $ORACLE_HOME/sqlplus/admin/glogin.sql.
Fixing a NOT NULL Constraint:
___________________________________________________________________________________________________________
Select table_name,constraint_name,search_condition from user_constraints
where table_name = 'EMPLOYEE'
SQL> /
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
EMPLOYEE SYS_C007007
"FNAME" IS NOT NULL
EMPLOYEE SYS_C007008
"LNAME" IS NOT NULL
EMPLOYEE SYS_C007009
"SSN" IS NOT NULL
etc etc
SQL> alter table EMPLOYEE drop constraint SYS_C007007;
__________________________________________________________________________________________________________
or
Alter table modify null
*TABLESSPACES CREATING:
SQL> CREATE TABLESPACE ts4 DATAFILE '/oradata/ts4_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
*Tables in a tablespace
select tablespace_name, table_name from all_tables where TABLESPACE_NAME
= 'CHUNG'
Adding Default tablespace to a user:
* Change users default tablespace:
SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE
USERNAME='SCOTT';
SQL> alter user scott default tablespace CHUNG;
* FIND THE PRIMARY KEYS
SELECT cols.table_name, cols.column_name, cols.position, cons.status,
cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
This sucks and is part of why oracle sucks
--------------000402010304020901050408
Content-Type: text/x-sql;
name="works_on_pk.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="works_on_pk.sql"
alter TABLE WORKS_ON add CONSTRAINT pk_works_on PRIMARY KEY(essn,pno)
/
--------------000402010304020901050408
Content-Type: text/x-sql;
name="works_on_insert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="works_on_insert.sql"
INSERT INTO WORKS_ON VALUES(123456789 ,1 ,32.5 );
INSERT INTO WORKS_ON VALUES(123456789 ,2 ,7.5 );
INSERT INTO WORKS_ON VALUES(666884444 ,3 ,40.0 );
INSERT INTO WORKS_ON VALUES(453453453 ,1 ,20.0 );
INSERT INTO WORKS_ON VALUES(453453453 ,2 ,20.0 );
INSERT INTO WORKS_ON VALUES(333445555 ,2 ,10.0 );
INSERT INTO WORKS_ON VALUES(333445555 ,3 ,10.0 );
INSERT INTO WORKS_ON VALUES(333445555 ,10 ,10.0 );
INSERT INTO WORKS_ON VALUES(333445555 ,20 ,10.0 );
INSERT INTO WORKS_ON VALUES(999887777 ,30 ,30.0 );
INSERT INTO WORKS_ON VALUES(999887777 ,10 ,10 );
INSERT INTO WORKS_ON VALUES(987987987 ,10 ,35.0 );
INSERT INTO WORKS_ON VALUES(987987987 ,30,5.0 );
INSERT INTO WORKS_ON VALUES(987654321 ,30 ,20 );
INSERT INTO WORKS_ON VALUES(987654321 ,20 ,15 );
INSERT INTO WORKS_ON VALUES(888665555 ,20 ,NULL );
--------------000402010304020901050408
Content-Type: text/x-sql;
name="show_keys.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="show_keys.sql"
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cols.table_name = 'EMPLOYEE'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position
/
--------------000402010304020901050408
Content-Type: text/x-sql;
name="show_keys_ruben.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="show_keys_ruben.sql"
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = 'RUBEN'
ORDER BY cols.table_name, cols.position
/
--------------000402010304020901050408
Content-Type: text/x-sql;
name="project_pk.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="project_pk.sql"
alter TABLE PROJECT add CONSTRAINT pk_project PRIMARY KEY(pnumber)
--------------000402010304020901050408
Content-Type: text/x-sql;
name="project_insert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="project_insert.sql"
INSERT INTO PROJECT VALUES('ProductX' ,1 ,'Bellaire',5 );
INSERT INTO PROJECT VALUES('ProductY' ,2 ,'Sugarland',5 );
INSERT INTO PROJECT VALUES('Productz' ,3 ,'Houston',5 );
INSERT INTO PROJECT VALUES('Computerization' ,10 ,'Stafford',4 );
INSERT INTO PROJECT VALUES('Reorganization' ,20 ,'Houston', 1 );
INSERT INTO PROJECT VALUES('Newbenifits' ,30 ,'Stafford',4 );
--------------000402010304020901050408
Content-Type: text/x-sql;
name="empy.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="empy.sql"
create table employee(
fname varchar2(20) NOT NULL,
minit char(1),
lname varchar2(20) NOT NULL,
ssn number(9) NOT NULL,
bdate date NOT NULL,
address varchar2(50) NOT NULL,
sex char(1) NOT NULL,
salary float(12) NOT NULL,
superssn number(9),
dno number(2)
);
create table dep_locations(
dnumber number(2) NOT NULL,
dlocation varchar2(20) NOT NULL
);
create table department(
dname varchar2(15) NOT NULL,
dnumber number(2) NOT NULL,
mgrssn number(9),
mgrstartdate date
);
create table works_on(
essn number(9),
pno number(2),
hours float(5)
);
create table project(
pname varchar2(20) NOT NULL,
pnumber number(2) NOT NULL,
plocation varchar2(15) NOT NULL,
dnum number(2)
);
create table dependent(
essn number(9) NOT NULL,
dependent_name varchar2(10) NOT NULL,
sex char(1) NOT NULL,
bdate date NOT NULL,
relationship varchar2(15) NOT NULL
);
commit
/
--------------000402010304020901050408
Content-Type: text/x-sql;
name="empy_insert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="empy_insert.sql"
insert into employee values ('John', 'B', 'Smith', 123456789, TO_DATE('1965-01-09', 'YYYY-MM-DD'), '731 Fondenm Houston, Tx', 'M', 3000, 333445555, 5);
insert into employee values ('Frankin', 'T', 'Wrong', 333445555 , TO_DATE('1955-12-08', 'YYYY-MM-DD'), '638 Voss, Houston, Tx', 'M', 4000, 888685555 , 5 );
insert into employee values ('Alica', 'J', 'Zelaya',999887777 , TO_DATE('1968-07-19', 'YYYY-MM-DD'), '3321 Castle Springs, Tx', 'F', 2500 ,987654321 ,4 );
insert into employee values ('Jenifer', 'S', 'Wallace', 987654321, TO_DATE('1961-05-20', 'YYYY-MM-DD'), '291 Barry St, Bellaire, Tx', 'F', '4300', 888685555 ,4 );
insert into employee values ('Ramesh', 'K', 'Narayan',556884444 , TO_DATE('1962-09-15', 'YYYY-MM-DD'), '975 Fire Oak, Humble, Tx', 'M', 3800,333445555 ,5 );
insert into employee values ('Joyce', 'A', 'English', 453453453, TO_DATE('1972-07-31', 'YYYY-MM-DD'), '5631 Rice St, Houston, Tx', 'F',2500 ,333445555 ,5 );
insert into employee values ('Ahmad', 'V', 'Jabbar', 987987987, TO_DATE('1969-03-29', 'YYYY-MM-DD'), '980 Dallas Street, Houston, Tx', 'M',2500, 987654321 , 5 );
insert into employee values ('James', 'E', 'Borg', 888665555, TO_DATE('1937-11-10', 'YYYY-MM-DD'), '450 Stone Street, Houston, Tx', 'M',55000 ,NULL , 1 );
insert into employee values ('Ruben', 'I', 'Safir', 264642882, TO_DATE('1963-04-01', 'YYYY-MM-DD'), '1580 East 19th Street, Brooklyn, NY', 'M',200000 ,NULL , 1 );
--------------000402010304020901050408
Content-Type: text/x-sql;
name="employee_pk.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="employee_pk.sql"
alter table employee add constraint pk_employee primary key (ssn)
/
--------------000402010304020901050408
Content-Type: text/x-sql;
name="dept_locations_insert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="dept_locations_insert.sql"
INSERT into DEP_LOCATIONS VALUES( 1, 'HOUSTON');
INSERT into DEP_LOCATIONS VALUES( 4, 'STADFORD');
INSERT into DEP_LOCATIONS VALUES( 5, 'BELLAIRE');
INSERT into DEP_LOCATIONS VALUES( 5, 'SUGARLAND');
INSERT into DEP_LOCATIONS VALUES( 5, 'HOUSTON');
--------------000402010304020901050408
Content-Type: text/x-sql;
name="dept_insert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="dept_insert.sql"
INSERT INTO DEPARTMENT VALUES( 'Research', 5, 333445555, TO_DATE('1988-05-22', 'YYYY-MM-DD'));
INSERT INTO DEPARTMENT VALUES( 'Administration', 4, 987654321, TO_DATE('1995-01-01', 'YYYY-MM-DD'));
INSERT INTO DEPARTMENT VALUES( 'Headquaters', 4, 888665555, TO_DATE('1988-05-22', 'YYYY-MM-DD'));
--------------000402010304020901050408
Content-Type: text/x-sql;
name="dep_locations_keys.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="dep_locations_keys.sql"
alter TABLE WORKS_ON add CONSTRAINT pk_works_on PRIMARY KEY(essn,pno)
/
--------------000402010304020901050408
Content-Type: text/x-sql;
name="dependent_pk.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="dependent_pk.sql"
alter TABLE DEPENDENT add CONSTRAINT pk_dependent PRIMARY KEY(essn,dependent_name)
--------------000402010304020901050408
Content-Type: text/x-sql;
name="dependent_insert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="dependent_insert.sql"
INSERT INTO DEPENDENT VALUES(333445555, 'Alice','F',TO_DATE('1986-04-05','YYYY-MM-DD' ),'DAUGHTER');
INSERT INTO DEPENDENT VALUES(333445555, 'Theodore','M',TO_DATE('1983-10-25','YYYY-MM-DD' ),'SON');
INSERT INTO DEPENDENT VALUES(333445555, 'JOY','F',TO_DATE('1958-05-03','YYYY-MM-DD' ),'SPOUSE');
INSERT INTO DEPENDENT VALUES(987654321, 'Abner','M',TO_DATE('1942-02-28','YYYY-MM-DD' ),'SPOUSE');
INSERT INTO DEPENDENT VALUES(123456789, 'Michael','M',TO_DATE('1988-01-04','YYYY-MM-DD' ),'SON');
INSERT INTO DEPENDENT VALUES(123456789, 'Alice','F',TO_DATE('1988-12-30','YYYY-MM-DD' ),'DAUGHTER');
INSERT INTO DEPENDENT VALUES(123456789, 'Elizabeth','F',TO_DATE('1967-05-05','YYYY-MM-DD' ),'SPOUSE');
--------------000402010304020901050408
Content-Type: text/x-sql;
name="department_pk.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="department_pk.sql"
alter TABLE DEPARTMENT add CONSTRAINT pk_department PRIMARY KEY(dnumber)
/
--------------000402010304020901050408--
|
|
