{"id":4637,"date":"2025-06-08T11:00:00","date_gmt":"2025-06-08T11:00:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4637"},"modified":"2025-07-07T15:20:41","modified_gmt":"2025-07-07T15:20:41","slug":"transfer-schema-statistics-from-source-to-target-database","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/transfer-schema-statistics-from-source-to-target-database\/","title":{"rendered":"Transfer Schema Statistics from Source to Target Database"},"content":{"rendered":"\n

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<\/code> and Data Pump<\/strong>, ensuring predictable execution plans and minimizing post-refresh tuning.<\/p>\n\n\n\n

Environment Details<\/h2>\n\n\n\n
<\/th>Source<\/th>Target<\/th><\/tr><\/thead>
OS<\/strong><\/td>Oracle Linux 7.x<\/td>Oracle Linux 7.x<\/td><\/tr>
DB Version<\/strong><\/td>19.14<\/td>19.14<\/td><\/tr>
Database Name<\/strong><\/td>hrdb<\/code><\/td>hrclone<\/code><\/td><\/tr>
Schema Name<\/strong><\/td>HR<\/code><\/td>HR<\/code><\/td><\/tr>
Host<\/strong><\/td>prod-db1.yourdomain.com<\/code><\/td>test-db1.yourdomain.com<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

1\ufe0f\u20e3 Prerequisites<\/h2>\n\n\n\n
-- Check if Data Pump directory exists:\nSELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'MY_DUMP_DIR';\n\n-- Example Output:\nDIRECTORY_NAME   DIRECTORY_PATH\n---------------  ---------------------------------------------\nMY_DUMP_DIR      \/u01\/app\/oracle\/admin\/hrdb\/dpdump\n\n-- If not present:\nCREATE OR REPLACE DIRECTORY MY_DUMP_DIR AS '\/u01\/app\/oracle\/admin\/hrdb\/dpdump';\nGRANT READ, WRITE ON DIRECTORY MY_DUMP_DIR TO HR;<\/code><\/code><\/pre>\n\n\n\n
2\ufe0f\u20e3 On Source Database (hrdb<\/code>)<\/h2>\n\n\n\n
-- Connect as SYS or HR\n\n-- 1. Create a table to store schema statistics\nEXEC DBMS_STATS.CREATE_STAT_TABLE('HR', 'HR_STATS_BACKUP');\n\n-- Verify creation:\nSELECT owner, object_name, object_type, created\nFROM dba_objects\nWHERE object_name = 'HR_STATS_BACKUP';\n\n-- Example Output:\nOWNER  OBJECT_NAME       OBJECT_TYPE  CREATED\n------ ----------------- ------------ -------------\nHR     HR_STATS_BACKUP   TABLE        07-JUL-25\n\n-- 2. Export schema statistics to the table\nEXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'HR', STATTAB => 'HR_STATS_BACKUP');\n\n-- Check row count\nSELECT COUNT(*) FROM HR.HR_STATS_BACKUP;\n\n-- Example Output:\nCOUNT(*)\n--------\n4200\n\n-- 3. Export the statistics table using Data Pump with compression and parallelism\nnohup 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 &<\/code><\/code><\/pre>\n\n\n\n
3\ufe0f\u20e3 Transfer Dump File to Target Server<\/h2>\n\n\n\n
# On the source server:\nscp \/u01\/app\/oracle\/admin\/hrdb\/dpdump\/hr_schema_stats_*.dmp oracle@test-db1.yourdomain.com:\/u01\/app\/oracle\/admin\/hrclone\/dpdump\/\n\n# Verify transferred files:\nssh oracle@test-db1.yourdomain.com \"ls -lh \/u01\/app\/oracle\/admin\/hrclone\/dpdump\/hr_schema_stats_*.dmp\"<\/code><\/code><\/pre>\n\n\n\n
4\ufe0f\u20e3 On Target Database (hrclone<\/code>)<\/h2>\n\n\n\n
-- Connect as SYS or HR\n\n-- 1. Optional: Backup current statistics before deletion\nEXEC DBMS_STATS.CREATE_STAT_TABLE('HR', 'HR_STATS_BEFORE_IMPORT');\nEXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'HR', STATTAB => 'HR_STATS_BEFORE_IMPORT');\n\n-- Check row count for reference\nSELECT COUNT(*) FROM HR.HR_STATS_BEFORE_IMPORT;\n\n--Example Output:\nCOUNT(*)\n--------\n4150\n\n-- 2. Delete existing statistics from the schema\nEXEC DBMS_STATS.DELETE_SCHEMA_STATS('HR');\n\n-- 3. Import the statistics table using Data Pump\nnohup 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 &\n\n-- 4. Verify imported data\nSELECT COUNT(*) FROM HR.HR_STATS_BACKUP;\n\n-- Example Output:\nCOUNT(*)\n--------\n4200\n\n-- 5. Import statistics back into the schema\nEXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME => 'HR', STATTAB => 'HR_STATS_BACKUP');<\/code><\/code><\/pre>\n\n\n\n
Optional: Importing Into a Different Schema<\/h2>\n\n\n\n
If importing into a different schema (NEW_HR<\/code>):<\/p>\n\n\n\n
-- Update schema references in the stats table:\nUPDATE NEW_HR.HR_STATS_BACKUP SET C5 = 'NEW_HR';\nCOMMIT;\n\n-- Then import:\nEXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME => 'NEW_HR', STATTAB => 'HR_STATS_BACKUP');<\/code><\/code><\/pre>\n\n\n\n
\u2705 Key Takeaways<\/h2>\n\n\n\n
\u2714\ufe0f Maintains consistent optimizer statistics across environments.<\/strong>
\u2714\ufe0f Avoids the need to regather statistics on large schemas post-clone.<\/strong>
\u2714\ufe0f Ensures stable SQL execution plans and predictable performance.<\/strong>
\u2714\ufe0f Saves time during environment refreshes and migrations.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
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 Source Target OS Oracle Linux 7.x Oracle Linux 7.x DB […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4637","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4637","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4637"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4637"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}