oracle example scott

清疚 2021-10-18 12:22 379阅读 0赞

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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

sql

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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

转载于:https://www.cnblogs.com/xiaodebing/p/10939870.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211018/dfa06ead99fe48a984b1b1efe0a7f76a.png