小伙伴们是不是安装完测试库或是想练习sql的环境中，没有官方示例库，其实就初始化实例图形界面中的dbca，勾选示例库，静默安装的话制定-sampleSchema true，甚至OCM考试中的那些HR、OE、SH这些都不知道表结构，还需要练习sql、做优化，是不是有点蒙。
因为23 ai RPM安装版本的测试库，无官方schema，每次测试都很痛苦，现在将官方的Smaple Schemas sql一次性导入库，演练起来。
官方描述：Oracle Database Sample Schemas
Oracle Database Sample Schemas
The new and improved Oracle Database 23c Sample Schemas contain several improvements:
All active data sets have been refreshed
Order Entry (OE) has been archived
Product Media (PM) has been archived
Schemas are installed independently from each other
SYS/SYSTEM user account access is no longer required
SQL*Loader is no longer required
The scripts are compatible with Oracle Database 19c and upwards, including Oracle Database Cloud Services.一、脚本位置与获取方式
1. 官方脚本路径，下载后放入对应路径
- HR/OE主脚本
--# HR
$ORACLE_HOME/demo/schema/human_resources/hr_main.sql
--# OE
$ORACLE_HOME/demo/schema/order_entry/oe_main.sql
包含用户创建、表结构、数据填充及约束。
- 依赖脚本
- hr_cre.sql（建表）
- hr_popul.sql（数据）
- oe_cre.sql、oe_popul.sql
优先使用下面建库一键SQL
2. GitHub资源
- 官方示例库：oracle-db-examples（含历史版本脚本）
- 独立备份库：hr-schema-scripts（Oracle维护）
- 发个baidu共享：通过网盘分享的文件：db-sample-schemas-23.3.zip
链接: https://pan.baidu.com/s/1CZWuhUUPHpmTigYqz3SQGg?pwd=e8tr 提取码: e8tr - Oracle Smaple Schema 从21c开始需手动安装，脚本需从GitHub下载后放入$ORACLE_HOME/demo/schema/对应目录
二、部署步骤（以HR为,OE、SH都要建表空间）
1. 环境准备
CREATE TABLESPACE hr_data
DATAFILE 'hr_data01.dbf' SIZE 100M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE hr_temp
TEMPFILE 'hr_temp01.dbf' SIZE 50M;2. 执行主脚本
sqlplus / as sysdba @$ORACLE_HOME/demo/schema/human_resources/hr_install.sql
安装过程中会交互输入：
- HR用户密码（如 hr_password）
- 默认表空间（HR_DATA）
- 临时表空间（HR_TEMP）
[oracle@OL97 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Sat Jun 14 18:27:21 2025
Version 23.8.0.25.04
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
SYS@FREE> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
SYS@FREE> alter session set container=FREEPDB1;
Session altered.
CREATE TABLESPACE hr_data
2 DATAFILE 'hr_data01.dbf' SIZE 100M AUTOEXTEND ON;
CREATE TEMPORARY TABLESPACE hr_temp
Tablespace created.
SYS@FREE> 2 TEMPFILE 'hr_temp01.dbf' SIZE 50M;
Tablespace created.
SYS@FREE> @/opt/oracle/demo/schema/db-sample-schemas-23.3/human_resources/hr_install.sql
Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.
Enter a password for the user HR:
Enter a tablespace for HR [USERS]: hr_data
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
****** Creating REGIONS table ....
****** Creating COUNTRIES table ....
****** Creating LOCATIONS table ....
****** Creating JOB_HISTORY table ....
Table created.
Index created.
Table altered.
****** Creating EMP_DETAILS_VIEW view ...
View created.
****** Creating indexes ...
Comment created.
Session altered.
****** Populating REGIONS table ....
PL/SQL procedure successfully completed.
****** Populating COUNTRIES table ....
PL/SQL procedure successfully completed.
****** Populating LOCATIONS table ....
PL/SQL procedure successfully completed.
****** Populating DEPARTMENTS table ....
Table altered.
PL/SQL procedure successfully completed.
****** Populating JOBS table ....
PL/SQL procedure successfully completed.
****** Populating EMPLOYEES table ....
PL/SQL procedure successfully completed.
****** Populating JOB_HISTORY table ....
PL/SQL procedure successfully completed.
Commit complete.
Table altered.
Trigger created.
Commit complete.
Installation
-------------
Verification:
Table provided actual
----------- ---------- ----------
regions 5 5
countries 25 25
departments 27 27
locations 23 23
employees 107 107
jobs 19 19
job_history 10 10
Thank you!
--------------------------------------------------------
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04
[oracle@OL97 ~]$3. 用户解锁与授权
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr_password;
-- 基础权限
GRANT CONNECT, RESOURCE, CREATE VIEW TO hr;
-- 细粒度访问控制，按照策略建
BEGIN
DBMS_RLS.ADD_POLICY('hr','employees','hr_policy','hr','emp_policy_fn','SELECT');
END;三、验证步骤
1. 连接与对象检查
-- 连接测试
sqlplus hr/Oracle_4U@orclpdb1
-- 表结构验证
SELECT table_name FROM user_tables; -- 应返回EMPLOYEES, DEPARTMENTS等
DESC employees; -- 查看字段结构
HR@localhost:1521/FREEPDB1> DESC EMPLOYEES;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@localhost:1521/FREEPDB1>2. 数据完整性校验
-- 示例数据查询
SELECT * FROM employees WHERE employee_id = 100; -- 应返回Steven King记录
SELECT COUNT(*) FROM employees; -- 107行为标准数据量
-- 外键约束验证
INSERT INTO departments (department_id) VALUES (999); -- 应触发主键缺失报错3. 权限与状态确认
SELECT username, account_status FROM dba_users WHERE username='HR'; -- 状态应为OPEN
SELECT * FROM dba_tab_privs WHERE grantee='HR'; -- 查看对象权限四、实操应用场景
1. SQL/PLSQL开发测试
-- 部门平均薪资查询
SELECT d.department_name, AVG(e.salary)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;
-- 条件编译示例（调试模式开关）
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:TRUE';
CREATE OR REPLACE PROCEDURE get_employee IS
BEGIN
$IF $$debug $THEN
DBMS_OUTPUT.PUT_LINE('Debug: Query started');
$END
-- 业务逻辑
END;2. 其他语言集成测试
- Python连接HR：
import cx_Oracle
conn = cx_Oracle.connect("hr", "hr_password", "localhost/orclpdb1")
cursor = conn.cursor()
cursor.execute("SELECT first_name, salary FROM employees")
for row in cursor: print(f"{row[0]}: {row[1]}")3. 安全与性能实践
- 权限最小化：
REVOKE DBA FROM hr; -- 回收高危权限
CREATE ROLE hr_limited;
GRANT SELECT ON hr.employees TO hr_limited; -- 按需授权- 索引优化：
CREATE INDEX emp_dept_idx ON employees(department_id); -- 加速部门查询
EXPLAIN PLAN FOR SELECT * FROM employees WHERE last_name='King'; -- 分析执行计划五、常见问题解决
问题 |
解决方案 |
脚本找不到 |
从GitHub下载db-sample-schemas并复制到$ORACLE_HOME/demo/schema/ |
HR用户被锁定 |
ALTER USER hr ACCOUNT UNLOCK; |
表空间不足 |
扩展表空间：ALTER TABLESPACE hr_data ADD DATAFILE 'hr_data02.dbf' SIZE 200M; |
