oracle example scott

  1 Rem Copyright (c) 1990, 2006, Oracle. All rights reserved.  
  2 Rem NAME
  3 REM    UTLSAMPL.SQL
  4 Rem  FUNCTION
  5 Rem  NOTES
  6 Rem  MODIFIED
  7 Rem     lburgess   04/02/06  - lowercase passwords 
  8 Rem     menash     02/21/01 -  remove unnecessary users for security reasons
  9 Rem     gwood      03/23/99 -  make all dates Y2K compliant
 10 Rem     jbellemo   02/27/97 -  dont connect as system
 11 Rem     akolk      08/06/96 -  bug 368261: Adding date formats
 12 Rem     glumpkin   10/21/92 -  Renamed from SQLBLD.SQL 
 13 Rem     blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
 14 Rem     rlim       04/29/91 -         change char to varchar2 
 15 Rem     mmoore     04/08/91 -         use unlimited tablespace priv 
 16 Rem     pritto     04/04/91 -         change SYSDATE to 13-JUL-87 
 17 Rem   Mendels    12/07/90 - bug 30123;add to_date calls so language independent
 18 Rem
 19 rem 
 20 rem $Header: utlsampl.sql 02-apr-2006.21:13:01 lburgess Exp $ sqlbld.sql 
 21 rem 
 22 SET TERMOUT OFF
 23 SET ECHO OFF
 24 
 25 rem CONGDON    Invoked in RDBMS at build time.   29-DEC-1988
 26 rem OATES:     Created: 16-Feb-83
 27  
 28 DROP USER SCOTT CASCADE;
 29 DROP USER ADAMS CASCADE;
 30 DROP USER JONES CASCADE;
 31 DROP USER CLARK CASCADE;
 32 DROP USER BLAKE CASCADE;
 33 GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;
 34 DROP PUBLIC SYNONYM PARTS;
 35 
 36 CONNECT SCOTT/tiger
 37 CREATE TABLE DEPT
 38        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
 39         DNAME VARCHAR2(14) ,
 40         LOC VARCHAR2(13) ) ;
 41 CREATE TABLE EMP
 42        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
 43         ENAME VARCHAR2(10),
 44         JOB VARCHAR2(9),
 45         MGR NUMBER(4),
 46         HIREDATE DATE,
 47         SAL NUMBER(7,2),
 48         COMM NUMBER(7,2),
 49         DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
 50 INSERT INTO DEPT VALUES
 51         (10,‘ACCOUNTING‘,‘NEW YORK‘);
 52 INSERT INTO DEPT VALUES (20,‘RESEARCH‘,‘DALLAS‘);
 53 INSERT INTO DEPT VALUES
 54         (30,‘SALES‘,‘CHICAGO‘);
 55 INSERT INTO DEPT VALUES
 56         (40,‘OPERATIONS‘,‘BOSTON‘);
 57 INSERT INTO EMP VALUES
 58 (7369,‘SMITH‘,‘CLERK‘,7902,to_date(‘17-12-1980‘,‘dd-mm-yyyy‘),800,NULL,20);
 59 INSERT INTO EMP VALUES
 60 (7499,‘ALLEN‘,‘SALESMAN‘,7698,to_date(‘20-2-1981‘,‘dd-mm-yyyy‘),1600,300,30);
 61 INSERT INTO EMP VALUES
 62 (7521,‘WARD‘,‘SALESMAN‘,7698,to_date(‘22-2-1981‘,‘dd-mm-yyyy‘),1250,500,30);
 63 INSERT INTO EMP VALUES
 64 (7566,‘JONES‘,‘MANAGER‘,7839,to_date(‘2-4-1981‘,‘dd-mm-yyyy‘),2975,NULL,20);
 65 INSERT INTO EMP VALUES
 66 (7654,‘MARTIN‘,‘SALESMAN‘,7698,to_date(‘28-9-1981‘,‘dd-mm-yyyy‘),1250,1400,30);
 67 INSERT INTO EMP VALUES
 68 (7698,‘BLAKE‘,‘MANAGER‘,7839,to_date(‘1-5-1981‘,‘dd-mm-yyyy‘),2850,NULL,30);
 69 INSERT INTO EMP VALUES
 70 (7782,‘CLARK‘,‘MANAGER‘,7839,to_date(‘9-6-1981‘,‘dd-mm-yyyy‘),2450,NULL,10);
 71 INSERT INTO EMP VALUES
 72 (7788,‘SCOTT‘,‘ANALYST‘,7566,to_date(‘13-JUL-87‘,‘dd-mm-rr‘)-85,3000,NULL,20);
 73 INSERT INTO EMP VALUES
 74 (7839,‘KING‘,‘PRESIDENT‘,NULL,to_date(‘17-11-1981‘,‘dd-mm-yyyy‘),5000,NULL,10);
 75 INSERT INTO EMP VALUES
 76 (7844,‘TURNER‘,‘SALESMAN‘,7698,to_date(‘8-9-1981‘,‘dd-mm-yyyy‘),1500,0,30);
 77 INSERT INTO EMP VALUES
 78 (7876,‘ADAMS‘,‘CLERK‘,7788,to_date(‘13-JUL-87‘, ‘dd-mm-rr‘)-51,1100,NULL,20);
 79 INSERT INTO EMP VALUES
 80 (7900,‘JAMES‘,‘CLERK‘,7698,to_date(‘3-12-1981‘,‘dd-mm-yyyy‘),950,NULL,30);
 81 INSERT INTO EMP VALUES
 82 (7902,‘FORD‘,‘ANALYST‘,7566,to_date(‘3-12-1981‘,‘dd-mm-yyyy‘),3000,NULL,20);
 83 INSERT INTO EMP VALUES
 84 (7934,‘MILLER‘,‘CLERK‘,7782,to_date(‘23-1-1982‘,‘dd-mm-yyyy‘),1300,NULL,10);
 85 CREATE TABLE BONUS
 86         (
 87         ENAME VARCHAR2(10)      ,
 88         JOB VARCHAR2(9)  ,
 89         SAL NUMBER,
 90         COMM NUMBER
 91         ) ;
 92 CREATE TABLE SALGRADE
 93       ( GRADE NUMBER,
 94         LOSAL NUMBER,
 95         HISAL NUMBER );
 96 INSERT INTO SALGRADE VALUES (1,700,1200);
 97 INSERT INTO SALGRADE VALUES (2,1201,1400);
 98 INSERT INTO SALGRADE VALUES (3,1401,2000);
 99 INSERT INTO SALGRADE VALUES (4,2001,3000);
100 INSERT INTO SALGRADE VALUES (5,3001,9999);
101 COMMIT;
102 EXIT

su - oracle
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
startup;


su - oracle
cd $ORACLE_HOME/dbs
rm lkORCL

/db/app/oracle/product/11.2.0/dbs/spfileorcl.ora
/db/app/oracle/oradata/orcl/control01.ctl
/db/app/oracle/flash_recovery_area/orcl/control02.ctl
/db/app/oracle/product/11.2.0/startup.log
/db/app/oracle/product/11.2.0/shutdown.log
/db/app/oracle/product/11.2.0//dbs/lkORCL
/db/app/oracle/product/11.2.0//bin/dbshut
/db/app/oracle/product/11.2.0//bin/dbstart
/db/app/oracle/product/11.2.0//bin/lsnrctl start
lsnrctl status
/db/app/oracle/product/11.2.0/network/admin/listener.ora

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.71)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )



/db/app/oracle/admin/orcl/pfile/init.ora.4122019134319


vi /etc/oratab
orcl:/db/app/oracle/product/11.2.0:Y

su - oracle
dbshut
dbstart