{"id":4129,"date":"2025-06-08T10:54:58","date_gmt":"2025-06-08T10:54:58","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4129"},"modified":"2025-06-15T20:21:36","modified_gmt":"2025-06-15T20:21:36","slug":"oracle-schema-refresh","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-schema-refresh\/","title":{"rendered":"Oracle Schema Refresh"},"content":{"rendered":"\n

Schema Refresh copies or synchronizes one or more schemas from a source Oracle database to a target database. It may involve replacing tables and objects fully, appending data to existing tables, skipping existing tables, remapping schemas or tablespaces, and more. Oracle Data Pump (expdp<\/code>\/impdp<\/code>) is the tool used.<\/p>\n\n\n\n

Steps on Source Database<\/h2>\n\n\n\n

1. Prechecks & Backup on Target (Plan Ahead)<\/h3>\n\n\n\n

Check if target schema exists:<\/p>\n\n\n\n

SELECT username FROM dba_users WHERE username = 'TARGET_SCHEMA';<\/code><\/code><\/pre>\n\n\n\n
Check row count for important tables in target schema:<\/p>\n\n\n\n
SELECT COUNT(*) FROM target_schema.some_table;<\/code><\/code><\/pre>\n\n\n\n
Backup target schema before refresh (important for revert):<\/p>\n\n\n\n
nohup expdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=target_schema_bkp_%U.dmp LOGFILE=target_schema_bkp.log SCHEMAS=target_schema COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &<\/code><\/code><\/pre>\n\n\n\n
2. Export Schema(s) on Source DB<\/h3>\n\n\n\n
Export single or multiple schemas:<\/p>\n\n\n\n
nohup expdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=source_schema_exp.log SCHEMAS=source_schema COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &<\/code><\/code><\/pre>\n\n\n\n
Export multiple schemas example:<\/p>\n\n\n\n
nohup expdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=multi_schemas_%U.dmp LOGFILE=multi_schemas_exp.log SCHEMAS=schema1,schema2 COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &<\/code><\/code><\/pre>\n\n\n\n
3. Transfer Dump Files to Target (If Needed)<\/h3>\n\n\n\n
If source and target don\u2019t share filesystem, transfer files using SCP:<\/p>\n\n\n\n
scp \/path\/to\/dump\/source_schema_*.dmp oracle@target_server:\/path\/to\/dump\/\nscp \/path\/to\/dump\/source_schema_exp.log oracle@target_server:\/path\/to\/dump\/<\/code><\/code><\/pre>\n\n\n\n
More info: SCP File Transfer<\/a><\/p>\n\n\n\n
Steps on Target Database<\/h2>\n\n\n\n
4. Create Directory Object (If Not Exists)<\/h3>\n\n\n\n
CREATE DIRECTORY my_dump_dir AS '\/path\/to\/dump';\nGRANT READ, WRITE ON DIRECTORY my_dump_dir TO target_user;<\/code><\/code><\/pre>\n\n\n\n
More info: Create Directory<\/a><\/p>\n\n\n\n
5. Lock Schema and Kill Sessions (If Schema Drop Is Planned)<\/h3>\n\n\n\n
If you’re performing a full refresh<\/strong> and plan to drop the target schema before import, follow these steps:<\/p>\n\n\n\n
Step 1: Lock the Schema<\/strong>
Prevent new connections to the schema:<\/p>\n\n\n\n
ALTER USER target_schema ACCOUNT LOCK;<\/code><\/code><\/pre>\n\n\n\n
\n
\u2705 This prevents any new sessions from being created during the drop\/import operation.<\/p>\n<\/blockquote>\n\n\n\n
Step 2: Kill Active Sessions<\/strong>
If active sessions exist that prevent the drop, generate kill commands:<\/p>\n\n\n\n
SELECT \n  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command\nFROM v$session\nWHERE username = 'TARGET_SCHEMA';<\/code><\/code><\/pre>\n\n\n\n
How to use:<\/strong><\/p>\n\n\n\n
    \n
  • Run the above query.<\/li>\n\n\n\n
  • It will return output like: <\/li>\n<\/ul>\n\n\n\n
    ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;\nALTER SYSTEM KILL SESSION '124,457' IMMEDIATE;<\/code><\/pre>\n\n\n\n
      \n
    • Copy and execute these statements in SQL*Plus or SQL Developer.<\/li>\n<\/ul>\n\n\n\n
      6. Drop Schema (Only If Doing Full Refresh)<\/h3>\n\n\n\n
      After locking and clearing sessions, drop the schema:<\/p>\n\n\n\n
      DROP USER target_schema CASCADE;<\/code><\/code><\/pre>\n\n\n\n
      \n
      \u26a0\ufe0f Only do this if you’re importing the entire schema fresh and don’t need any existing objects.<\/p>\n<\/blockquote>\n\n\n\n
      7. Import Schema \u2014 Different Types of Schema Refresh<\/h3>\n\n\n\n
      Type<\/th>When to Use<\/th>Import Command Example<\/th><\/tr><\/thead>
      Full Refresh (Drop & Import)<\/strong><\/td>Drop existing schema & import fresh<\/td>— Kill sessions (step 5) and drop schema (step 6) first<\/em>
      nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log SCHEMAS=target_schema TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &<\/code><\/td><\/tr>
      Full Refresh (Replace Tables)<\/strong><\/td>Replace all tables without drop<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log SCHEMAS=target_schema TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &<\/code><\/td><\/tr>
      Append Data<\/strong><\/td>Add new rows to existing tables<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log SCHEMAS=target_schema TABLE_EXISTS_ACTION=APPEND PARALLEL=4 &<\/code><\/td><\/tr>
      Skip Existing Tables<\/strong><\/td>Import only tables that don\u2019t exist<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log SCHEMAS=target_schema TABLE_EXISTS_ACTION=SKIP PARALLEL=4 &<\/code><\/td><\/tr>
      Truncate & Load<\/strong><\/td>Truncate tables before import (keep structure)<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log SCHEMAS=target_schema TABLE_EXISTS_ACTION=TRUNCATE PARALLEL=4 &<\/code><\/td><\/tr>
      Remap Schema Names<\/strong><\/td>When source and target schema names differ<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log REMAP_SCHEMA=source_schema:target_schema TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &<\/code><\/td><\/tr>
      Remap Tablespaces<\/strong><\/td>Source tablespace missing on target, remap TS<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=source_schema_%U.dmp LOGFILE=target_schema_imp.log SCHEMAS=target_schema REMAP_TABLESPACE=old_ts:new_ts TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &<\/code><\/td><\/tr>
      Multiple Schemas<\/strong><\/td>Import multiple schemas at once<\/td>nohup impdp '\"\/ as sysdba\"' DIRECTORY=my_dump_dir DUMPFILE=multi_schemas_%U.dmp LOGFILE=multi_schemas_imp.log SCHEMAS=target_schema1,target_schema2 TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      Important Parameters Summary (Useful for Schema Refresh)<\/h2>\n\n\n\n
      nohup expdp|impdp '\"\/ as sysdba\"' DIRECTORY=directory_name\n  DUMPFILE=your_table_%U.dmp [,more_files]\n  LOGFILE=logfile_name.log\n  FULL=Y|N\n  SCHEMAS=schema_name [,schema_name]\n  TABLES=table_name [,table_name] or owner.tablename\n  QUERY=table_name:\"WHERE condition\"\n  TABLE_EXISTS_ACTION=REPLACE|APPEND|TRUNCATE|SKIP\n  PARALLEL=number_of_threads\n  REUSE_DUMPFILES=Y|N\n  COMPRESSION=ALL|DATA|METADATA|NONE\n  CONTENT=ALL|DATA_ONLY|METADATA_ONLY\n  NETWORK_LINK=network_link_name\n  FLASHBACK_TIME=\"timestamp\"\n  ENCRYPTION=ALL|DATA|METADATA|NONE\n  ENCRYPTION_PASSWORD=password\n  DISABLE_ARCHIVE_LOGGING=Y|N\n  ROWS=Y|N\n  VERSION=version_number\n  JOB_NAME=job_name<\/code><\/pre>\n\n\n\n
      When defining DUMPFILE<\/code> in expdp<\/code> or impdp<\/code>, you can use the following dynamic placeholders to auto-generate unique and informative file names:<\/p>\n\n\n\n
      Parameter<\/th>Meaning<\/th>Example Output<\/th><\/tr><\/thead>
      %U<\/code><\/td>Universal unique file part (used for PARALLEL)<\/td>schema_%U.dmp<\/code> \u2192 schema_01.dmp<\/code>, schema_02.dmp<\/code>, …<\/td><\/tr>
      %I<\/code><\/td>Instance number (RAC environments)<\/td>schema_%I_%U.dmp<\/code> \u2192 schema_1_01.dmp<\/code>, schema_2_01.dmp<\/code><\/td><\/tr>
      %p<\/code><\/td>Job name prefix<\/td>schema_%p_%U.dmp<\/code> \u2192 schema_myjob_01.dmp<\/code><\/td><\/tr>
      %j<\/code><\/td>Job name<\/td>schema_%j_%U.dmp<\/code> \u2192 schema_myjob_01.dmp<\/code><\/td><\/tr>
      %d<\/code><\/td>Directory object name<\/td>schema_%d_%U.dmp<\/code> \u2192 schema_MIG_DIR_01.dmp<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      Notes:<\/h3>\n\n\n\n
        \n
      • %U<\/code><\/strong> is the most widely used<\/strong>\u2014ensures unique dump files in parallel jobs<\/strong>.<\/li>\n\n\n\n
      • %I<\/code><\/strong> helps distinguish files by instance in RAC<\/strong> setups.<\/li>\n\n\n\n
      • %p<\/code><\/strong> and %j<\/code><\/strong> allow identification of job-specific<\/strong> dumps.<\/li>\n\n\n\n
      • %d<\/code><\/strong> includes the directory object name<\/strong>, useful for environments using multiple directories.<\/li>\n<\/ul>\n\n\n\n
        \n
        \ud83d\udccc Recommended format for schema refresh:<\/strong><\/p>\n<\/blockquote>\n\n\n\n
        DUMPFILE=schema_name_%U.dmp<\/code><\/code><\/pre>\n\n\n\n
        \n
        Or with job-specific context:<\/p>\n<\/blockquote>\n\n\n\n
        DUMPFILE=schema_%j_%U.dmp<\/code><\/code><\/pre>\n\n\n\n
        Notes<\/h3>\n\n\n\n
          \n
        • Always back up the target schema<\/strong> before performing a refresh.<\/li>\n\n\n\n
        • Use TABLE_EXISTS_ACTION=REPLACE<\/code> to drop and recreate<\/strong> tables during import. This is useful when doing a full refresh of existing objects.<\/li>\n\n\n\n
        • If the schema cannot be dropped due to active sessions, use the session kill block<\/strong> to terminate them before drop.<\/li>\n\n\n\n
        • %U<\/code> is essential for parallel export\/import<\/strong>, allowing the generation of multiple dump files for faster processing.<\/li>\n\n\n\n
        • Use REMAP_SCHEMA<\/code> when importing into a schema with a different name<\/strong> than the source.<\/li>\n\n\n\n
        • Use REMAP_TABLESPACE<\/code> if the source tablespace<\/strong> does not exist on the target \u2014 to remap to an available target tablespace.<\/li>\n\n\n\n
        • Use parallelism<\/strong> (PARALLEL=4<\/code> or higher) to significantly speed up both export and import operations.<\/li>\n\n\n\n
        • Ensure the directory object exists<\/strong> on the target and the target user has READ\/WRITE<\/strong> privileges on it.<\/li>\n\n\n\n
        • Always adjust parameters<\/strong> according to your environment, data volume, and performance requirements.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
          Schema Refresh copies or synchronizes one or more schemas from a source Oracle database to a target database. It may involve replacing tables and objects fully, appending data to existing tables, skipping existing tables, remapping schemas or tablespaces, and more. Oracle Data Pump (expdp\/impdp) is the tool used. Steps on Source Database 1. Prechecks & […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4129","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\/4129","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=4129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}