Clone schema within same database

Question

I'm using Oracle 12.1. The database I'm talking about is used for build purpose and we need to create db schema which is compatible with the version of code in use.

For example, we have say schema1 which is prod-like but without data. This schema is exported executing expdp command.

When I want to build my code, the process will create schema2 by executing impdp command against the file used to export schema1. Once my code is built, schema2 will be exported and will be available for other codes to be built and so on.

The problem is that expdp and impdp commands are time consuming. expdp takes around 20-25 mins and impdp takes around 7-10 mins even though only metadata is exported/imported. Since these operations are against same database, I was wondering if there is a better way to achieve "cloning" of schema.

I'm using v12.1 at the moment, but will soon upgrade to v19.2 If any feature is introduced in 19.2 then I can use that as well.

Commands used are:

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

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

Here is output of expdp command

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

<< edit >> tkprof shows this internal sql to be most time consuming one -

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

Answer 1

Master table

Use the master table to debug export and import performance. The master table is similar to the expdp/impdp output files, but it contains much more details.

The master table is always created for an export or import, but it's usually dropped when the command finishes. To keep it, add keep_master=y to the data pump commands.

The name of the table will be at the end out the data pump command output. For example, if the last line of the output is Job "YOUR_USER"."SYS_EXPORT_SCHEMA_01" completed..., then you want to query the table YOUR_USER.SYS_EXPORT_SCHEMA_01.

You can use a query like this to find the slowest part of the export or import commands:

select
    start_time,
    (lead(start_time) over (order by start_time) - start_time) * 60*60*24 seconds_diff,
    exp.*
from jheller.sys_export_schema_01 exp
where start_time is not null
order by seconds_diff desc;

[Edit: Looks like METRICS=YES does almost the same thing but in an easier output format.]

Transportable tablespace

I also recommend giving transportable tablespaces a try. Sending a single binary file as-is will likely be much faster than sending commands to recreate the data. (Although there will still be some time required to re-populate the data dictionary. I created an example of using transportable tablespaces to copy a schema in the same database in this answer.

Gather statistics

Try gathering more statistics to improve the data dictionary queries that are slowing down the export. Fixed object and dictionary statistics are rarely gathered because they don't change as rapidly as tables with user data. But if you haven't gathered them in years, there may be huge differences that lead to poor performance when querying DBA* and V$* views.

Run this command to gather fixed objects and dictionary statistics. These commands may run very slowly, but you'll only need to run them like once a year or less.

begin
    dbms_stats.gather_fixed_objects_stats;
    dbms_stats.gather_dictionary_stats;
end;
/

In fact, if you Google "7wn3wubg7gjds", it's a notoriously slow query, and one of the possible solutions listed on My Oracle Support is to regather statistics.