34-Oracle 23 ai 示例数据库部署指南、脚本获取、验证与实操（兼容19c）

关注

原创最新推荐文章于 2026-08-15 22:34:25 发布 1.2k 阅读 AI 写同款· GEO 优化 ›

Oracle19c静默建库实战：如何用dbca.rsp模板快速生成生产级数据库？本文详细介绍了Oracle19c静默建库的实战方法，重点解析如何通过dbca.rsp响应文件快速生成生产级数据库。内容涵盖模板选择、参数优化、性能调优及故障排查，特别适用于OpenEuler等国产化操作系统环境下的自动化部署，大幅提升DBA工作效率。阅读详情

小伙伴们是不是安装完测试库或是想练习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;