Oracle Schema Refresh

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 & Backup on Target (Plan Ahead)

Check if target schema exists:

SELECT username FROM dba_users WHERE username = 'TARGET_SCHEMA';

Check row count for important tables in target schema:

SELECT COUNT(*) FROM target_schema.some_table;

Backup target schema before refresh (important for revert):

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 &

2. Export Schema(s) on Source DB

Export single or multiple schemas:

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 &

Export multiple schemas example:

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 &

3. Transfer Dump Files to Target (If Needed)

If source and target don’t share filesystem, transfer files using SCP:

scp /path/to/dump/source_schema_*.dmp oracle@target_server:/path/to/dump/
scp /path/to/dump/source_schema_exp.log oracle@target_server:/path/to/dump/

More info: SCP File Transfer

Steps on Target Database

4. Create Directory Object (If Not Exists)

CREATE DIRECTORY my_dump_dir AS '/path/to/dump';
GRANT READ, WRITE ON DIRECTORY my_dump_dir TO target_user;

More info: Create Directory

5. Lock Schema and Kill Sessions (If Schema Drop Is Planned)

If you’re performing a full refresh and plan to drop the target schema before import, follow these steps:

Step 1: Lock the Schema
Prevent new connections to the schema:

ALTER USER target_schema ACCOUNT LOCK;

✅ This prevents any new sessions from being created during the drop/import operation.

Step 2: Kill Active Sessions
If active sessions exist that prevent the drop, generate kill commands:

SELECT 
  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command
FROM v$session
WHERE username = 'TARGET_SCHEMA';

How to use:

• Run the above query.
• It will return output like:

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;
ALTER SYSTEM KILL SESSION '124,457' IMMEDIATE;

• Copy and execute these statements in SQL*Plus or SQL Developer.

6. Drop Schema (Only If Doing Full Refresh)

After locking and clearing sessions, drop the schema:

DROP USER target_schema CASCADE;

⚠️ Only do this if you’re importing the entire schema fresh and don’t need any existing objects.

7. Import Schema — Different Types of Schema Refresh

Important Parameters Summary (Useful for Schema Refresh)

nohup expdp|impdp '"/ as sysdba"' DIRECTORY=directory_name
  DUMPFILE=your_table_%U.dmp [,more_files]
  LOGFILE=logfile_name.log
  FULL=Y|N
  SCHEMAS=schema_name [,schema_name]
  TABLES=table_name [,table_name] or owner.tablename
  QUERY=table_name:"WHERE condition"
  TABLE_EXISTS_ACTION=REPLACE|APPEND|TRUNCATE|SKIP
  PARALLEL=number_of_threads
  REUSE_DUMPFILES=Y|N
  COMPRESSION=ALL|DATA|METADATA|NONE
  CONTENT=ALL|DATA_ONLY|METADATA_ONLY
  NETWORK_LINK=network_link_name
  FLASHBACK_TIME="timestamp"
  ENCRYPTION=ALL|DATA|METADATA|NONE
  ENCRYPTION_PASSWORD=password
  DISABLE_ARCHIVE_LOGGING=Y|N
  ROWS=Y|N
  VERSION=version_number
  JOB_NAME=job_name

When defining DUMPFILE in expdp or impdp, you can use the following dynamic placeholders to auto-generate unique and informative file names:

Notes:

• %U is the most widely used—ensures unique dump files in parallel jobs.
• %I helps distinguish files by instance in RAC setups.
• %p and %j allow identification of job-specific dumps.
• %d includes the directory object name, useful for environments using multiple directories.

📌 Recommended format for schema refresh:

DUMPFILE=schema_name_%U.dmp

Or with job-specific context:

DUMPFILE=schema_%j_%U.dmp

Notes

• Always back up the target schema before performing a refresh.
• Use TABLE_EXISTS_ACTION=REPLACE to drop and recreate tables during import. This is useful when doing a full refresh of existing objects.
• If the schema cannot be dropped due to active sessions, use the session kill block to terminate them before drop.
• %U is essential for parallel export/import, allowing the generation of multiple dump files for faster processing.
• Use REMAP_SCHEMA when importing into a schema with a different name than the source.
• Use REMAP_TABLESPACE if the source tablespace does not exist on the target — to remap to an available target tablespace.
• Use parallelism (PARALLEL=4 or higher) to significantly speed up both export and import operations.
• Ensure the directory object exists on the target and the target user has READ/WRITE privileges on it.
• Always adjust parameters according to your environment, data volume, and performance requirements.