Oracle12C手工添加SCOTT/HR模式
2020-11-14 15:07 EniNiemand 阅读(460) 评论(0) 编辑 收藏 举报在实验环境建库过程中,示例模式没有勾选,所以在创建好的数据库中,没有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;