Oracle 12c 建库后，没有 scott 模式，本篇使用手工脚本方式，在12c版本中创建 scott 模式及相关表。
目录
1. PDB中创建用户
2. PDB中用户授权
3. PDB中创建表空间
4. PDB中设置用户默认表空间
5. 运行脚本文件
6. 设置TNSNAMES.ora
7. 连接测试验证数据
1. PDB中创建用户
[oracle@henry ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 22:00:08 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED
--------- ------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
SQL> alter session set container=pdb2; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ----------- ---------- ----------
4 PDB2 READ WRITE NO
SQL> create user scott identified by tiger; User created.
2. PDB中创建表空间
创建USERS表空间（测试环境名字可以自定义）。
注意语句没有指定路径，是因为这里在PDB中设置了参数 db_create_file_dest，启用了OMF（Oracle Managed Files），文件不用指定路径，自动管理。
SQL> create tablespace users datafile size 100m; Tablespace created. SQL> select name from v$tablespace; NAME
-----------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
3. PDB中用户授权
SQL> grant connect,resource,unlimited tablespace to scott; Grant succeeded.
4. PDB中设置用户默认表空间
SQL> alter user scott default tablespace users; User altered. SQL> alter user scott temporary tablespace temp; User altered.
5. 运行脚本文件
SQL> @scott.sql
内容
CREATE TABLE "SCOTT"."BONUS"
("ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"SAL" NUMBER,
"COMM" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."DEPT"
("DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."EMP"
("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"; CREATE TABLE "SCOTT"."SALGRADE"
("GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"; REM INSERTING into SCOTT.BONUS
SET DEFINE OFF;
REM INSERTING into SCOTT.DEPT
SET DEFINE OFF;
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
REM INSERTING into SCOTT.EMP
SET DEFINE OFF;
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
REM INSERTING into SCOTT.SALGRADE
SET DEFINE OFF;
Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (1,700,1200);
Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (2,1201,1400);
Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (3,1401,2000);
Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (4,2001,3000);
Insert into SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (5,3001,9999); CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ; CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ; ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
COMMIT;
scott.sql
6. 设置TNSNAMES.ora
[oracle@henry ~]$ cd $ORACLE_HOME/network/admin
[oracle@henry admin]$ vim tnsnames.ora
添加
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = henry)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB2)
)
)
7. 连接测试验证数据
[oracle@henry admin]$ sqlplus scott/tiger@pdb2 SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 22:45:21 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show con_name CON_NAME
------------------------------
PDB2
SQL> select count(*) from emp; COUNT(*)
----------
14 SQL> select count(*) from dept; COUNT(*)
----------
4