Table Refresh Using EXPDP-IMPDP Datapump Command

January 26, 2021

Step by Step guide for Table Refresh using DataPump Utility

Oracle Data Pump is a high-speed data movement utility provided by Oracle (10g onwards). It’s an extension to the old EXP and IMP utility with improved performance and enhanced security features.

Requirement of Table refresh:

Table refresh is a common task that is quite often asked to perform by an Oracle Database Admin. The reason to perform this task often comes as a request from the application team. Whenever there is a need arises to test or perform some activity on the data, the data needs to be copied from a higher environment (PROD) to a lower environment( DEV, QA, TEST, etc). Whenever there is a need for defragmenting any objects in the database we can also perform this activity.

The way to get this data across is via refresh/datapump as described below:

Here we are performing a table refresh activity using Datapump:

*PARAMETERS*	*SOURCE*	*TARGET*
DATABASE	LAB02	LAB03
HOSTNAME	MACHINE1	MACHINE1
TABLE_NAME	TABLE1	TABLE1
TABLE_NAME	TABLE1	TABLENEW

Pre-checks before performing Table Level Export-Import:

Before performing import in the target database, we can perform the following pre-checks to avoid any last-minute error :

Check the space utilization of table(s) before export and prior import, check whether the table(s) exists or not in the target database, required space should be available for import in the target database.
Check the tablespaces associated with the source table so that we can compare the same in the target database. If required we can create the tablespace or use the parameter REMAP_TABLESPACE if we want to import table in a different tablespace other than the source table’s tablespace.
If the table already exists in the target database, we can use the parameter TABLE_EXISTS_ACTION in impdp or drop the object before starting the import.
Note down the object and its status and count prior to export and post-import.

Click here to check for the datapump directory creation steps in detail.
Click here to check for the table’s size.

To Perform Data Pump Export (expdp)

To perform Table export :

[oracle@machine1 lab03]$ expdp dumpfile=TABLE1.dmp directory=TABLE1 logfile=TABLE1.log TABLES=TESTUSER1.TABLE1

Export: Release 12.1.0.2.0 - Production on Sat Jan 16 16:11:36 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=TABLE1.dmp directory=TABLE1 logfile=TABLE1.log TABLES=TESTUSER1.TABLE1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TESTUSER1"."TABLE1"                        1.976 MB   19846 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oradata/expdp/lab02/TABLE1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 16 16:12:55 2021 elapsed 0 00:01:11

To Perform Data Pump Import (impdp)

To perform Table import:

This is one instance where if the table already exists in the target database, we can first drop the existing table and then import it into the target database:

[oracle@machine1 lab03]$ impdp dumpfile=TABLE1.dmp directory=TABLE1 logfile=TABLE1.log TABLES=TESTUSER1.TABLE1

Import: Release 12.1.0.2.0 - Production on Sat Jan 16 16:22:50 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA dumpfile=TABLE1.dmp directory=TABLE1 logfile=TABLE1.log TABLES=TESTUSER1.TABLE1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."TABLE1"                        1.976 MB   19846 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jan 16 16:23:37 2021 elapsed 0 00:00:39

2. Using the REMAP_TABLESPACE parameter in the case of the below scenarios:

a) Dependent tablespace doesn’t exist in the target database.

b) Purposely, we want to import the particular object in a specific tablespace.

impdp DUMPFILE=TABLE1.dmp DIRECTORY=TABLE1 LOGFILE=TABLE1.log TABLES=TESTUSER1.TABLE1 REMAP_TABLESPACE=USERS:TBLS2

Import: Release 12.1.0.2.0 - Production on Sat Jan 23 15:46:06 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA DUMPFILE=TABLE1.dmp DIRECTORY=TABLE1 LOGFILE=TABLE1.log TABLES=TESTUSER1.TABLE1 REMAP_TABLESPACE=USERS:TBLS2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."TABLE1"                        1.976 MB   19846 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jan 23 15:46:59 2021 elapsed 0 00:00:47

3. Using the REMAP_TABLE parameter :

a)We can use this parameter if we want to import the desired table with a different name in the target database.

[oracle@machine1 ~]$ impdp DUMPFILE=TABLE1.dmp DIRECTORY=TABLE1 LOGFILE=TABLE1.log TABLES=TESTUSER1.TABLE1 REMAP_TABLE=TESTUSER1.TABLE1:TABLENEW

Import: Release 12.1.0.2.0 - Production on Sat Jan 23 15:54:59 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA DUMPFILE=TABLE1.dmp DIRECTORY=TABLE1 LOGFILE=TABLE1.log TABLES=TESTUSER1.TABLE1 REMAP_TABLE=TESTUSER1.TABLE1:TABLENEW
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."TABLENEW"                      1.976 MB   19846 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jan 23 15:55:08 2021 elapsed 0 00:00:04

Validate the import log and /row object count should be the same as export.
Check all objects and their status after import. If any invalid object please recompile it.

Click here for How to validate invalid objects in Oracle

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.