{"id":4127,"date":"2025-06-08T10:04:16","date_gmt":"2025-06-08T10:04:16","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4127"},"modified":"2025-06-08T10:04:17","modified_gmt":"2025-06-08T10:04:17","slug":"oracle-table-refresh","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-table-refresh\/","title":{"rendered":"Oracle Table Refresh"},"content":{"rendered":"\n

Table Refresh<\/strong> 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.<\/p>\n\n\n\n

Steps on Target Database (Prechecks & Backup)<\/h2>\n\n\n\n
\n

Although you may plan on source, these must run on target DB before refresh:<\/p>\n<\/blockquote>\n\n\n\n

1. Check if table exists on target<\/h3>\n\n\n\n
SELECT table_name FROM all_tables WHERE owner = 'TARGET_SCHEMA' AND table_name = 'YOUR_TABLE';<\/code><\/code><\/pre>\n\n\n\n
2. Check row count on target<\/h3>\n\n\n\n
SELECT COUNT(*) FROM target_schema.your_table;<\/code><\/code><\/pre>\n\n\n\n
3. Backup target table (for revert safety)<\/h3>\n\n\n\n
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 &<\/code><\/code><\/pre>\n\n\n\n
Steps on Source Database (Export)<\/h2>\n\n\n\n
4. Export table from source DB<\/h3>\n\n\n\n
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 &<\/code><\/code><\/pre>\n\n\n\n
Steps on Target Database (Import)<\/h2>\n\n\n\n
5. Transfer dump files to target if source and target don\u2019t share filesystem<\/h3>\n\n\n\n
scp \/path\/to\/dump\/your_table_*.dmp oracle@target_server:\/path\/to\/dump\/\nscp \/path\/to\/dump\/your_table_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
6. Create Directory Object on target (if not present)<\/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
7. Import Table(s) \u2014 Different Types of Table Refresh (choose as per need)<\/h3>\n\n\n\n
Type<\/th>When to Use<\/th>Import Command Example<\/th><\/tr><\/thead>
Full Refresh (REPLACE)<\/strong><\/td>Replace entire table data<\/td>nohup 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 &<\/code><\/td><\/tr>
Append Data (APPEND)<\/strong><\/td>Add new rows to existing data<\/td>nohup 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 &<\/code><\/td><\/tr>
Truncate & Load (TRUNCATE)<\/strong><\/td>Delete all rows first, then load new data<\/td>nohup 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 &<\/code><\/td><\/tr>
Skip If Exists (SKIP)<\/strong><\/td>Import only if table does not exist<\/td>nohup 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 &<\/code><\/td><\/tr>
Subset Rows (QUERY)<\/strong><\/td>Refresh partial data (filtered rows)<\/td>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 &<\/code>
Import as full refresh example<\/td><\/tr>
Multiple Tables<\/strong><\/td>Refresh multiple tables together<\/td>Export:
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 &<\/code>
Import as full refresh example<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Quick Reference \u2014 Important expdp\/impdp Parameters for Table 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><\/code><\/pre>\n\n\n\n
For Oracle Data Pump dump file naming, besides %U<\/code>, these are the common substitution parameters you can use in the DUMPFILE<\/strong> parameter:<\/p>\n\n\n\n
Parameter<\/th>Meaning<\/th>Example<\/th><\/tr><\/thead>
%U<\/code><\/td>Universal file name \u2014 automatically generated unique dump file part (used for parallel jobs)<\/td>your_table_%U.dmp<\/code> \u2192 your_table_01.dmp<\/code>, your_table_02.dmp<\/code>, etc.<\/td><\/tr>
%I<\/code><\/td>Instance number (useful in RAC environments)<\/td>your_table_%I_%U.dmp<\/code> \u2192 your_table_1_01.dmp<\/code>, your_table_2_01.dmp<\/code><\/td><\/tr>
%p<\/code><\/td>Job name prefix<\/td>your_table_%p_%U.dmp<\/code> \u2192 your_table_myjob_01.dmp<\/code><\/td><\/tr>
%j<\/code><\/td>Job name<\/td>your_table_%j_%U.dmp<\/code> \u2192 your_table_myjob_01.dmp<\/code> (similar to %p<\/code>)<\/td><\/tr>
%d<\/code><\/td>Directory object name<\/td>your_table_%d_%U.dmp<\/code> \u2192 your_table_my_dir_01.dmp<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Notes:<\/h3>\n\n\n\n
    \n
  • %U<\/code> is most commonly used because it guarantees unique file names for parallel jobs.<\/li>\n\n\n\n
  • %I<\/code> is particularly useful in RAC to differentiate files by instance.<\/li>\n\n\n\n
  • %p<\/code> and %j<\/code> are based on the job name, making it easier to identify dump files belonging to a specific job.<\/li>\n\n\n\n
  • %d<\/code> adds the directory object name, useful if you manage multiple directories.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4127","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\/4127","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=4127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}