Oracle12C手工添加SCOTT/HR模式

在实验环境建库过程中，示例模式没有勾选，所以在创建好的数据库中，没有HR模式，为了后面的实验，在此手工添加SCOTT模式（因为SCOTT模式下的表名，列名比较短，且方便记忆），添加HR模式也是一样的流程。

SCOTT模式的脚本也已经上传到了 /software/目录下。

1. 切换到Oracle用户

2. 登录进数据库（在ERP中添加SCOTT模式）

3.执行SCOTT模式脚本

具体命令如下：

  1 切换到Oracle用户
  2 [root@oracle12c ~]# su - oracle
  3 Last login: Sat Nov 14 12:14:47 CST 2020 on pts/0
  4 [oracle@oracle12c ~]$ sqlplus / as sysdba
  5 
  6 SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 14 14:53:43 2020
  7 
  8 Copyright (c) 1982, 2016, Oracle.  All rights reserved.
  9 
 10 
 11 Connected to:
 12 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 13 
 14 SQL> show pdbs
 15 
 16     CON_ID CON_NAME              OPEN MODE  RESTRICTED
 17 ---------- ------------------------------ ---------- ----------
 18      2 PDB$SEED              READ ONLY  NO
 19      3 ERP                  READ WRITE NO
 20 
 21 切换到ERP中
 22 SQL> alter session set container=erp;
 23 
 24 Session altered.
 25 
 26 执行SCOTT模式脚本
 27 SQL> @/software/scottdemo.sql
 28 
 29 User dropped.
 30 
 31 
 32 User created.
 33 
 34 
 35 Grant succeeded.
 36 
 37 
 38 User altered.
 39 
 40 
 41 User altered.
 42 
 43 
 44 Table created.
 45 
 46 
 47 Table created.
 48 
 49 
 50 Table created.
 51 
 52 
 53 Table created.
 54 
 55 
 56 1 row created.
 57 
 58 
 59 1 row created.
 60 
 61 
 62 1 row created.
 63 
 64 
 65 1 row created.
 66 
 67 
 68 1 row created.
 69 
 70 
 71 1 row created.
 72 
 73 
 74 1 row created.
 75 
 76 
 77 1 row created.
 78 
 79 
 80 1 row created.
 81 
 82 
 83 1 row created.
 84 
 85 
 86 1 row created.
 87 
 88 
 89 1 row created.
 90 
 91 
 92 1 row created.
 93 
 94 
 95 1 row created.
 96 
 97 
 98 1 row created.
 99 
100 
101 1 row created.
102 
103 
104 1 row created.
105 
106 
107 1 row created.
108 
109 
110 1 row created.
111 
112 
113 1 row created.
114 
115 
116 1 row created.
117 
118 
119 1 row created.
120 
121 
122 1 row created.
123 
124 
125 Index created.
126 
127 
128 Index created.
129 
130 
131 Table altered.
132 
133 
134 Table altered.
135 
136 
137 Table altered.
138 
139 
140 Commit complete.
141 
142 SQL>

附上SCOTT模式脚本中的代码：

  1 drop user scott cascade;
  2 create user scott identified by tiger;
  3 grant connect,resource,unlimited tablespace to scott;
  4 alter user scott default tablespace users;
  5 alter user scott temporary tablespace temp;
  6 CREATE TABLE "SCOTT"."BONUS" 
  7    ("ENAME" VARCHAR2(10 BYTE), 
  8     "JOB" VARCHAR2(9 BYTE), 
  9     "SAL" NUMBER, 
 10     "COMM" NUMBER
 11    ) SEGMENT CREATION DEFERRED 
 12   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 13   TABLESPACE "USERS" ;
 14  
 15   CREATE TABLE "SCOTT"."DEPT" 
 16    ("DEPTNO" NUMBER(2,0), 
 17     "DNAME" VARCHAR2(14 BYTE), 
 18     "LOC" VARCHAR2(13 BYTE)
 19    ) SEGMENT CREATION IMMEDIATE 
 20   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 21   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 22   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 23   TABLESPACE "USERS" ;
 24 
 25   CREATE TABLE "SCOTT"."EMP" 
 26    ("EMPNO" NUMBER(4,0), 
 27     "ENAME" VARCHAR2(10 BYTE), 
 28     "JOB" VARCHAR2(9 BYTE), 
 29     "MGR" NUMBER(4,0), 
 30     "HIREDATE" DATE, 
 31     "SAL" NUMBER(7,2), 
 32     "COMM" NUMBER(7,2), 
 33     "DEPTNO" NUMBER(2,0)
 34    ) SEGMENT CREATION IMMEDIATE 
 35   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 36   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 37   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 38   TABLESPACE "USERS";
 39  
 40   CREATE TABLE "SCOTT"."SALGRADE" 
 41    ("GRADE" NUMBER, 
 42     "LOSAL" NUMBER, 
 43     "HISAL" NUMBER
 44    ) SEGMENT CREATION IMMEDIATE 
 45   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 46   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 47   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 48   TABLESPACE "USERS";
 49   
 50 REM INSERTING into SCOTT.BONUS
 51 SET DEFINE OFF;
 52 REM INSERTING into SCOTT.DEPT
 53 SET DEFINE OFF;
 54 Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
 55 Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
 56 Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
 57 Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
 58 REM INSERTING into SCOTT.EMP
 59 SET DEFINE OFF;
 60 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
 61 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
 62 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
 63 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
 64 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
 65 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
 66 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
 67 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
 68 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
 69 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
 70 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
 71 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
 72 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
 73 Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
 74 REM INSERTING into SCOTT.SALGRADE
 75 SET DEFINE OFF;
 76 Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (1,700,1200);
 77 Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (2,1201,1400);
 78 Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (3,1401,2000);
 79 Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (4,2001,3000);
 80 Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (5,3001,9999);
 81  
 82 CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO") 
 83 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
 84 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 85 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 86 TABLESPACE "USERS" ;
 87  
 88 CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") 
 89 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
 90 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 91 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 92 TABLESPACE "USERS" ;
 93  
 94 ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
 95 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
 96 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 97 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 98 TABLESPACE "USERS"  ENABLE;
 99  
100 ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
101 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
102 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
103 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
104 TABLESPACE "USERS"  ENABLE;
105  
106 ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
107 REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
108 COMMIT;