Master in Data Analyst |Batch Starting From 26 May 2025 at 6 PM IST| Register for Free Demo

Know More

Oracle datapump utility -EXPDP IMPDP

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 07 Jun, 2019
  • 0 Comments
  • 4 Mins Read

Oracle datapump utility -EXPDP IMPDP

In this post we will be looking for logical backup of oracle database. datapump (EXPDP and IMPDP) utilties are used to take import and export of oracle databases.

Mode of Datapump

  1. Entire database
  2. Schema level
  3. Table level
  4. Tablespace level
  5. Transportable tablespace level

Use of Datapump

  1. Moving data from one schema to another
  2. Moving data from one version of Oracle to another
  3. Moving data from one OS to another
  4. Creating logical backups

Create Directory and Grant permission on directory.

CREATE OR REPLACE DIRECTORY test_dir AS '/data/backup';

 GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Table backup

 expdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1.log 

 impdp tables=test.testtable directory=test_dir dumpfile=testtable1.dmp logfile=testtable1_imp.log

Schema Backup

 expdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema.log 

 impdp schemas=test directory=test_dir dumpfile=testtable1_schema.dmp logfile=testtable1_schema_imp.log

Full Database Backup

 expdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_db.log 

 impdp full=Y directory=test_dir dumpfile=full_db.dmp logfile=full_IMP_db.log

Include and Exclude

 expdp schemas=TEST include=TABLE:\""IN ('TESTTABLE')\"" directory=TEST_DIR dumpfile=test_include_testtable.dmp logfile=test_include_testtable.log 

 expdp schemas=TEST exclude=TABLE:\""IN ('EXCLUDE_TESTTABLE_NAME')\"" directory=TEST_DIR dumpfile=test_exclude_testtable.dmp logfile=test_exclude_testtable.log

CONTENT and QUERY

 expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_content.dmp logfile=TEST_content.log content=METADATA_ONLY 

 expdp schemas=TEST directory=TEST_DIR dumpfile=TEST_data_only.dmp logfile=TEST_data_only.log content=DATA_ONLY 

 expdp full=Y directory=TEST_DIR dumpfile=TEST_query.dmp logfile=TEST_query.log query='test.testtable:"WHERE id=0"'

LOGTIME PARAMETER

If you are looking to print the logtime then from 12c its possible. It will print the logtime of the object which are going to exported with datapump.

Following are the parameter can be put with LOGTIME.

LOGTIME=[NONE | STATUS | LOGFILE | ALL]

NONE : The default value, which indicates that no timestamps should be included in the output, making the output look similar to that of previous versions.

STATUS : Timestamps are included in output to the console, but not in the associated log file.

LOGFILE : Timestamps are included in output to the log file, but not in the associated console messages.

ALL : Timestamps are included in output to the log file and console.

 expdp tables=C##TESTUSER.TESTTABLE directory=TEST_DIR dumpfile=C##TESTUSER_logfile.dmp logfile=C##TESTUSER_logfile.log LOGTIME=ALL  

FLASHBACK Parameter In DATAPUMP(EXPDP)

We can use the flashback parameter ( FLASHBACK_SCN or FLASHBACK_TIMESTAMP) in expdp to take a consistent backup of the table as of a specific point in time. 

 SQL> SET numwidth 20
 SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL; 

GET_SYSTEM_CHANGE_NUMBER 
------------------------ 
                 2139371

Now prepare PARFILE with FLASHBACK_SCN: ( Use the SCN value from step 1)

cat exp_flash.par

dumpfile=dbaclass.dmp logfile=dbaclass.log directory=EXPDIR tables=DBACLASS.OBJECT_LIST flashback_scn=2139371
Now use this PARFILE with expdp. 
 
expdp parfile=exp_flash.par

Get Current SCN Value : 

 SQL> select current_scn from v$database;
 Get SCN Value at particular time : 
 SQL> select timestamp_to_scn('15-AUG-23 :22:00:10') from dual;
Get timestamp Value from SCN : 
 SQL> select scn_to_timestamp(<SCN_Number>) from dual;

Remap table:

syntax : 
 remap_table=schema.table_name:new_table
example : 
[oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees remap_table=hr.employees:emp_bak dumpfile=full.dmp

Remap Multiple Tables : There are 2 ways to remap multiple tables :

1. Delimited by Commas : Every mapping set should be delimited by a comma. 

 [oracle@test ~]$ impdp system/oracle@pdbprim tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak,hr.jobs:job_bak dumpfile=test.dmp
2. Repeated REMAP_TABLE : Delimiting by commas may not feasible for hundreds of table remapping, so you may split them by the parameter REMAP_TABLE. 
 [oracle@test ~]$ impdp system/password@orclpdb tables=hr.employees,hr.jobs remap_table=hr.employees:emp_bak remap_table=hr.jobs:job_bak dumpfile=full.dmp

Remap tablespace:

         While importing tables , we can get error ORA-00959:tablespace doesnot exist, to solve this error or to prevent this error, we can create same tablespace in the target database or remap tablespace. The parameter we should use in data pump (impdp) is REMAP_TABLESPACE . Syntax : 

 REMAP_TABLESPACE=<SOURCE_TABLESPACE>:<TARGET_TABLESPACE>
For example: 
 

[oracle@test ~]$ impdp system/oracle@pdbprim schemas=TESTUSER REMAP_TABLESPACE=TEST_TBS_01:TEST_TBS_02 dumpfile=test.dmp

Remap Schema: If schema names are different in source and target , you must remap schema names. add the following line in import command : 
remap_schema=<op_source>:<op_target>
Oracle Tablespace assign to user
Tablespaces in oracle database | Tablespaces script