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, 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‘,1600,300,30);
 61 INSERT INTO EMP VALUES
 62 (7521,‘WARD‘,to_date(‘22-2-1981‘,1250,500,30);
 63 INSERT INTO EMP VALUES
 64 (7566,‘JONES‘,‘MANAGER‘,7839,to_date(‘2-4-1981‘,2975,20);
 65 INSERT INTO EMP VALUES
 66 (7654,‘MARTIN‘,to_date(‘28-9-1981‘,1400,30);
 67 INSERT INTO EMP VALUES
 68 (7698,‘BLAKE‘,to_date(‘1-5-1981‘,2850,30);
 69 INSERT INTO EMP VALUES
 70 (7782,‘CLARK‘,to_date(‘9-6-1981‘,2450,10);
 71 INSERT INTO EMP VALUES
 72 (7788,‘SCOTT‘,‘ANALYST‘,7566,to_date(‘13-JUL-87‘,‘dd-mm-rr‘)-85,3000,20);
 73 INSERT INTO EMP VALUES
 74 (7839,‘KING‘,‘PRESIDENT‘,to_date(‘17-11-1981‘,5000,10);
 75 INSERT INTO EMP VALUES
 76 (7844,‘TURNER‘,to_date(‘8-9-1981‘,1500,0,30);
 77 INSERT INTO EMP VALUES
 78 (7876,‘ADAMS‘,7788,‘dd-mm-rr‘)-51,1100,20);
 79 INSERT INTO EMP VALUES
 80 (7900,‘JAMES‘,to_date(‘3-12-1981‘,950,30);
 81 INSERT INTO EMP VALUES
 82 (7902,‘FORD‘,20);
 83 INSERT INTO EMP VALUES
 84 (7934,‘MILLER‘,7782,to_date(‘23-1-1982‘,1300,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

sql

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

note