Transfer Schema Statistics from Source to Target Database

Maintaining consistent optimizer statistics across environments is crucial for stable SQL performance during migrations and refreshes. This guide explains how to export and import Oracle schema statistics safely and efficiently using DBMS_STATS and Data Pump, ensuring predictable execution plans and minimizing post-refresh tuning.

Environment Details

1️⃣ Prerequisites

-- Check if Data Pump directory exists:
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'MY_DUMP_DIR';

-- Example Output:
DIRECTORY_NAME   DIRECTORY_PATH
---------------  ---------------------------------------------
MY_DUMP_DIR      /u01/app/oracle/admin/hrdb/dpdump

-- If not present:
CREATE OR REPLACE DIRECTORY MY_DUMP_DIR AS '/u01/app/oracle/admin/hrdb/dpdump';
GRANT READ, WRITE ON DIRECTORY MY_DUMP_DIR TO HR;

2️⃣ On Source Database (hrdb)

-- Connect as SYS or HR

-- 1. Create a table to store schema statistics
EXEC DBMS_STATS.CREATE_STAT_TABLE('HR', 'HR_STATS_BACKUP');

-- Verify creation:
SELECT owner, object_name, object_type, created
FROM dba_objects
WHERE object_name = 'HR_STATS_BACKUP';

-- Example Output:
OWNER  OBJECT_NAME       OBJECT_TYPE  CREATED
------ ----------------- ------------ -------------
HR     HR_STATS_BACKUP   TABLE        07-JUL-25

-- 2. Export schema statistics to the table
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'HR', STATTAB => 'HR_STATS_BACKUP');

-- Check row count
SELECT COUNT(*) FROM HR.HR_STATS_BACKUP;

-- Example Output:
COUNT(*)
--------
4200

-- 3. Export the statistics table using Data Pump with compression and parallelism
nohup expdp '"/ as sysdba"' DIRECTORY=MY_DUMP_DIR DUMPFILE=hr_schema_stats_%U.dmp LOGFILE=hr_schema_stats_exp.log TABLES=HR.HR_STATS_BACKUP COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &

3️⃣ Transfer Dump File to Target Server

# On the source server:
scp /u01/app/oracle/admin/hrdb/dpdump/hr_schema_stats_*.dmp oracle@test-db1.yourdomain.com:/u01/app/oracle/admin/hrclone/dpdump/

# Verify transferred files:
ssh oracle@test-db1.yourdomain.com "ls -lh /u01/app/oracle/admin/hrclone/dpdump/hr_schema_stats_*.dmp"

4️⃣ On Target Database (hrclone)

-- Connect as SYS or HR

-- 1. Optional: Backup current statistics before deletion
EXEC DBMS_STATS.CREATE_STAT_TABLE('HR', 'HR_STATS_BEFORE_IMPORT');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'HR', STATTAB => 'HR_STATS_BEFORE_IMPORT');

-- Check row count for reference
SELECT COUNT(*) FROM HR.HR_STATS_BEFORE_IMPORT;

--Example Output:
COUNT(*)
--------
4150

-- 2. Delete existing statistics from the schema
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('HR');

-- 3. Import the statistics table using Data Pump
nohup impdp '"/ as sysdba"' DIRECTORY=MY_DUMP_DIR DUMPFILE=hr_schema_stats_%U.dmp LOGFILE=hr_schema_stats_imp.log TABLES=HR.HR_STATS_BACKUP TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &

-- 4. Verify imported data
SELECT COUNT(*) FROM HR.HR_STATS_BACKUP;

-- Example Output:
COUNT(*)
--------
4200

-- 5. Import statistics back into the schema
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME => 'HR', STATTAB => 'HR_STATS_BACKUP');

Optional: Importing Into a Different Schema

If importing into a different schema (NEW_HR):

-- Update schema references in the stats table:
UPDATE NEW_HR.HR_STATS_BACKUP SET C5 = 'NEW_HR';
COMMIT;

-- Then import:
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME => 'NEW_HR', STATTAB => 'HR_STATS_BACKUP');

✅ Key Takeaways

✔️ Maintains consistent optimizer statistics across environments.
✔️ Avoids the need to regather statistics on large schemas post-clone.
✔️ Ensures stable SQL execution plans and predictable performance.
✔️ Saves time during environment refreshes and migrations.