ADVERTISEMENT

Oracle Table Refresh

Table Refresh means updating or replacing one or more tables on a target database from a source database using Data Pump (expdp/impdp), minimizing downtime and ensuring consistent data.

Steps on Target Database (Prechecks & Backup)

Although you may plan on source, these must run on target DB before refresh:

1. Check if table exists on target

SELECT table_name FROM all_tables WHERE owner = 'TARGET_SCHEMA' AND table_name = 'YOUR_TABLE';

2. Check row count on target

SELECT COUNT(*) FROM target_schema.your_table;

3. Backup target table (for revert safety)

nohup expdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_bkp_%U.dmp LOGFILE=your_table_bkp.log TABLES=target_schema.your_table COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &

Steps on Source Database (Export)

4. Export table from source DB

nohup expdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_%U.dmp LOGFILE=your_table_exp.log TABLES=source_schema.your_table COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &

Steps on Target Database (Import)

5. Transfer dump files to target if source and target don’t share filesystem

scp /path/to/dump/your_table_*.dmp oracle@target_server:/path/to/dump/
scp /path/to/dump/your_table_exp.log oracle@target_server:/path/to/dump/

More info: SCP File Transfer

6. Create Directory Object on target (if not present)

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

More info: Create Directory

7. Import Table(s) — Different Types of Table Refresh (choose as per need)

TypeWhen to UseImport Command Example
Full Refresh (REPLACE)Replace entire table datanohup impdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_%U.dmp LOGFILE=your_table_imp.log TABLES=your_table TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &
Append Data (APPEND)Add new rows to existing datanohup impdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_%U.dmp LOGFILE=your_table_imp.log TABLES=your_table TABLE_EXISTS_ACTION=APPEND PARALLEL=4 &
Truncate & Load (TRUNCATE)Delete all rows first, then load new datanohup impdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_%U.dmp LOGFILE=your_table_imp.log TABLES=your_table TABLE_EXISTS_ACTION=TRUNCATE PARALLEL=4 &
Skip If Exists (SKIP)Import only if table does not existnohup impdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_%U.dmp LOGFILE=your_table_imp.log TABLES=your_table TABLE_EXISTS_ACTION=SKIP PARALLEL=4 &
Subset Rows (QUERY)Refresh partial data (filtered rows)Export with QUERY:
nohup expdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=your_table_%U.dmp LOGFILE=your_table_exp.log TABLES=your_table QUERY="WHERE created_date > TO_DATE('2024-01-01','YYYY-MM-DD')" COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &
Import as full refresh example
Multiple TablesRefresh multiple tables togetherExport:
nohup expdp '"/ as sysdba"' DIRECTORY=my_dump_dir DUMPFILE=multi_tables_%U.dmp LOGFILE=multi_tables_exp.log TABLES=table1,table2,table3 COMPRESSION=ALL PARALLEL=4 REUSE_DUMPFILES=Y &
Import as full refresh example

Quick Reference — Important expdp/impdp Parameters for Table 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

For Oracle Data Pump dump file naming, besides %U, these are the common substitution parameters you can use in the DUMPFILE parameter:

ParameterMeaningExample
%UUniversal file name — automatically generated unique dump file part (used for parallel jobs)your_table_%U.dmp → your_table_01.dmp, your_table_02.dmp, etc.
%IInstance number (useful in RAC environments)your_table_%I_%U.dmp → your_table_1_01.dmp, your_table_2_01.dmp
%pJob name prefixyour_table_%p_%U.dmp → your_table_myjob_01.dmp
%jJob nameyour_table_%j_%U.dmp → your_table_myjob_01.dmp (similar to %p)
%dDirectory object nameyour_table_%d_%U.dmp → your_table_my_dir_01.dmp

Notes:

  • %U is most commonly used because it guarantees unique file names for parallel jobs.
  • %I is particularly useful in RAC to differentiate files by instance.
  • %p and %j are based on the job name, making it easier to identify dump files belonging to a specific job.
  • %d adds the directory object name, useful if you manage multiple directories.

ADVERTISEMENT