Oracle 19c添加scott用户和表
Oracle 12c之后推出了可插拔数据库，在一个容器cdb中以多租户的形式同时存在多个数据库pdb。pdb中默认不包含scott用户。学习SQL，添加scott用户时需要在pdb中添加。
oracle 19c自带了scott的脚本，安装Oracle之后在$ORACLE_HOME/rdbms/admin路径下存在utlsampl.sql文件，该sql文件功能即是创建scott用户和表。步骤如下：
1、为pdb添加tnsnames
1[oracle@oracle ~]$ vim $ORACLE_HOME/network/admin/tnsnames.ora
2# tnsnames.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
3# Generated by Oracle configuration tools.
4
5ORCLCDB =
6 (DESCRIPTION =
7 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
8 (CONNECT_DATA =
9 (SERVER = DEDICATED)
10 (SERVICE_NAME = ORCLCDB)
11 )
12 )
13
14LISTENER_ORCLCDB =
15 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
16
17#添加PDB，SERVICE_NAME可以通过show pdbs
18ORCLPDB =
19 (DESCRIPTION =
20 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
21 (CONNECT_DATA =
22 (SERVER = DEDICATED)
23 (SERVICE_NAME = ORCLPDB1)
24 )
25 )
26
2、修改utlsampl.sql
将 CONNECT SCOTT/tiger 改为 CONNECT SCOTT/tiger@ORCLPDB
3、使用sysdba登录sqlplus，切换到要导入的容器pdb ,执行 utlsampl.sql
1[oracle@oracle ~]$ sqlplus / as sysdba
2
3SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 10:42:44 2021
4Version 19.3.0.0.0
5
6Copyright (c) 1982, 2019, Oracle. All rights reserved.
7
8
9Connected to:
10Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
11Version 19.3.0.0.0
12
13SQL> show con_name
14
15CON_NAME
16------------------------------
17CDB$ROOT
18SQL> show pdbs;
19
20 CON_ID CON_NAME OPEN MODE RESTRICTED
21---------- ------------------------------ ---------- ----------
22 2 PDB$SEED READ ONLY NO
23 3 ORCLPDB1 READ WRITE NO
24SQL> alter session set container=ORCLPDB1;
25
26Session altered.
27
28SQL> show con_name;
29
30CON_NAME
31------------------------------
32ORCLPDB1
33SQL> @$ORACLE_HOME/rdbms/admin/utlsampl.sql
34Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
35Version 19.3.0.0.0
36[oracle@oracle ~]$
- 查看scott用户和表
1[oracle@oracle ~]$ sqlplus / as sysdba
2
3SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 10:45:57 2021
4Version 19.3.0.0.0
5
6Copyright (c) 1982, 2019, Oracle. All rights reserved.
7
8
9Connected to:
10Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
11Version 19.3.0.0.0
12
13SQL> alter session set container=ORCLPDB1;
14
15Session altered.
16
17## 如果数据库ORCLPDB1未打开,可执行
18
19## SQL> alter database ORCLPDB1 open;
20
21## Database altered.
22
23SQL> select username from dba_users where username = 'SCOTT';
24
25USERNAME
26--------------------------------------------------------------------------------
27SCOTT
28
29SQL> conn scott/tiger@ORCLPDB
30Connected.
31SQL> select table_name from user_tables;
32
33TABLE_NAME
34--------------------------------------------------------------------------------
35DEPT
36EMP
37BONUS
38SALGRADE
39
40SQL> select * from emp;
41
42 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
43---------- ---------- --------- ---------- --------- ---------- ---------- ----------
44 7369 SMITH CLERK 7902 17-DEC-80 800 20
45 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
46 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
47 7566 JONES MANAGER 7839 02-APR-81 2975 20
48 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
49 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
50 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
51 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
52 7839 KING PRESIDENT 17-NOV-81 5000 10
53 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
54 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
55 7900 JAMES CLERK 7698 03-DEC-81 950 30
56 7902 FORD ANALYST 7566 03-DEC-81 3000 20
57 7934 MILLER CLERK 7782 23-JAN-82 1300 10
58
5914 rows selected.
60
61SQL>