Oracle 12.1/19.2同库Schema克隆高效方案咨询

架构老林

2026-7-2

Oracle 12.1/19.2 同库Schema克隆优化方案咨询

背景

当前使用Oracle 12.1版本作为构建环境数据库，需创建与代码版本兼容的Schema。现有流程：

以无数据的类生产Schema（schema1）为基础，用expdp导出元数据
代码构建时，通过impdp将导出文件导入为新Schema（schema2）
代码构建完成后，schema2再被导出供后续构建使用

问题

仅元数据导出/导入耗时过长：

expdp需20-25分钟
impdp需7-10分钟
因操作在同一数据库内执行，寻求更高效的Schema克隆方法，同时可使用即将升级的Oracle 19.2版本新特性。

当前使用命令

expdp命令

expdp <user>/<pwd>@<db> DIRECTORY=<dir> DUMPFILE=<file> CONTENT=METADATA_ONLY SCHEMAS=<schema1> LOGFILE=<somelog.txt> EXCLUDE=STATISTICS

impdp命令

impdp <user>/<pwd>@<db> DIRECTORY=<dir> DUMPFILE=<file> REMAP_SCHEMA=<schema1>:<schema2> SCHEMAS=<schema1> LOGFILE=<somelog.txt> TRANSFORM=OID:N EXCLUDE=PASSWORD_HISTORY

expdp命令输出

Starting "<schema>"."SYS_EXPORT_SCHEMA_06":  <schema>/********@<db> DIRECTORY=<dir> DUMPFILE=<schema>.sch CONTENT=METADATA_ONLY SCHEMAS=<schema1> LOGFILE=logfile.txt EXCLUDE=STATISTICS METRICS=YES
Startup took 1 seconds
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 14 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 4 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 1 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
     Completed 2 TABLESPACE_QUOTA objects in 0 seconds
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
     Completed 1 PASSWORD_HISTORY objects in 1 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 3 seconds
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
     Completed 6 TYPE objects in 157 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
     Completed 49 SEQUENCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 10 OBJECT_GRANT objects in 22 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 6671 TABLE objects in 64 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 6128 OBJECT_GRANT objects in 52 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
     Completed 2890 COMMENT objects in 15 seconds
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
     Completed 1 IDENTITY_COLUMN objects in 2 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
     Completed 241 PACKAGE objects in 2 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 76 OBJECT_GRANT objects in 22 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 87 PROCEDURE objects in 43 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 91 OBJECT_GRANT objects in 22 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
     Completed 241 ALTER_PACKAGE_SPEC objects in 1 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 87 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 41 VIEW objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 115 OBJECT_GRANT objects in 22 seconds
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
     Completed 236 PACKAGE_BODY objects in 567 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 1678 INDEX objects in 114 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
     Completed 26 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 112 CONSTRAINT objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 1 REF_CONSTRAINT objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 117 TRIGGER objects in 2 seconds
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
     Completed 3 TRIGGER objects in 3 seconds
Master table "FXO_BLD"."SYS_EXPORT_SCHEMA_06" successfully loaded/unloaded
Dump file set for FXO_BLD.SYS_EXPORT_SCHEMA_06 is:
  <dir>/<schema>.sch
Job "<schema>"."SYS_EXPORT_SCHEMA_07" successfully completed at Fri Jan 19 07:50:04 2024 elapsed 0 00:18:46

TKPROF分析补充

耗时最长的内部SQL：

SQL ID: 7wn3wubg7gjds Plan Hash: 0

BEGIN :1 := sys.kupc$que_int.get_status(:2, :3); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    363      0.30    1601.88          0       7601          0         726
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      363      0.30    1601.88          0       7601          0         726

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  wait for unread message on broadcast channel
                                                386        5.00       1601.62

优化方案

一、Oracle 12.1适用优化手段

DBMS_METADATA批量导出/导入
直接通过PL/SQL调用DBMS_METADATA.GET_DDL获取所有对象的DDL语句，替换Schema名称后在新用户下执行，避免导出文件IO开销，还可并行处理对象：

-- 示例：导出schema1的所有表DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'SCHEMA1') FROM ALL_TABLES WHERE OWNER = 'SCHEMA1';

可编写脚本批量导出表、视图、存储过程、包等所有对象类型。

expdp/impdp参数优化

添加PARALLEL参数（如PARALLEL=4），根据CPU核心数设置并行度，提升处理速度
若无需详细日志，设置NOLOGFILE=Y减少日志写入开销
确保DIRECTORY对应的存储为SSD等高性能磁盘

专属表空间克隆（Schema独占表空间时）
若schema1有专属表空间，可通过以下步骤快速克隆：

备份原表空间数据文件，恢复为新表空间
创建新用户schema2，指定默认表空间为新表空间
导入元数据时跳过表空间创建，直接映射到新表空间

二、Oracle 19.2专属优化特性

Schema在线快速克隆
Oracle 19c引入在线Schema克隆功能，无需导出导入，直接在库内复制元数据，操作耗时大幅缩短：

CREATE USER schema2 CLONE schema1 IDENTIFIED BY <password>
  DEFAULT TABLESPACE <tablespace>
  TEMPORARY TABLESPACE temp
  NO DATA;

增强型并行元数据导入
19c强化了impdp并行处理能力，配合以下参数进一步提速：

impdp <user>/<pwd>@<db> DIRECTORY=<dir> DUMPFILE=<file> REMAP_SCHEMA=<schema1>:<schema2> 
SCHEMAS=<schema1> LOGFILE=<somelog.txt> TRANSFORM=OID:N EXCLUDE=PASSWORD_HISTORY
PARALLEL=8 METADATA_COMPRESSION=ALL

三、当前expdp等待事件优化

从TKPROF结果看，耗时集中在wait for unread message on broadcast channel事件，属于并行进程间通信等待问题：

调整PARALLEL参数，避免过高设置引发进程竞争
检查JOB_QUEUE_PROCESSES参数，确保有足够进程支持并行操作
确保数据库服务器CPU、内存资源充足，避免资源瓶颈

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

Oracle 12.1/19.2同库Schema克隆高效方案咨询

背景 #

问题 #

当前使用命令 #

expdp命令 #

impdp命令 #

expdp命令输出 #

TKPROF分析补充 #

优化方案 #

一、Oracle 12.1适用优化手段 #

二、Oracle 19.2专属优化特性 #

三、当前expdp等待事件优化 #

背景

问题