如何在Oracle Database 21c XE中安装SCOTT/TIGER演示schema

Oracle 21c开始官方不再默认附带传统的SCOTT等演示Schema，所以ORACLE_HOME/sqlplus/demo目录下没有对应文件属于正常情况，可按照以下步骤手动完成安装：

操作前置要求 #

使用具有DBA权限的用户（如SYS、SYSTEM）操作，所有操作需在21c XE默认的可插拔数据库XEPDB1中执行，不要操作CDB根容器。

具体操作步骤 #

1. 登录sqlplus，连接到XEPDB1

sqlplus system/你设置的SYSTEM用户密码@//localhost:1521/XEPDB1

如果不清楚PDB名称，可使用SYS登录CDB后执行以下命令查询：

SELECT name, open_mode FROM v$pdbs;

2. 创建SCOTT用户
   如果创建时提示密码不符合规则或公用用户名无效，先执行会话参数修改：

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

再执行创建用户命令：

CREATE USER scott IDENTIFIED BY tiger 
DEFAULT TABLESPACE users 
TEMPORARY TABLESPACE temp 
ACCOUNT UNLOCK;

3. 给SCOTT用户授权

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE SYNONYM TO scott;
ALTER USER scott QUOTA UNLIMITED ON users;

4. 切换到SCOTT用户

CONNECT scott/tiger@//localhost:1521/XEPDB1

5. 创建演示表并插入测试数据
   依次执行以下SQL：

-- 创建部门表DEPT
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

-- 创建员工表EMP
CREATE TABLE EMP (
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

-- 创建工资等级表SALGRADE
CREATE TABLE SALGRADE (
    GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

-- 创建奖金表BONUS
CREATE TABLE BONUS (
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER,
    COMM NUMBER
);

-- 提交所有变更
COMMIT;

6. 验证安装结果
   执行以下查询，返回14条员工记录即为安装成功：

SELECT COUNT(*) FROM EMP;

注意事项 #

• 所有操作必须在可插拔数据库中执行，禁止在CDB$ROOT根容器创建普通用户，避免破坏系统配置
• _ORACLE_SCRIPT参数仅用于测试环境安装演示Schema，生产环境不要开启该参数，需按照密码规则设置复杂度符合要求的用户密码

内容的提问来源于stack exchange，提问作者ilsurealism