Oracle 12C Datapump- EXPDP-IMPDP

Datapump is the utility to take the logical backup of the oracle databases. Following can be take with the help of datapump.

• Tables
• Tablespaces
• Full Database Logical Backup
• Users
• Index
• Stats
• Metadata

Following command will create the logical directory which will point to os physical level directory.

First Create the physical directory :

 [oracle@oracle ~]$ mkdir -p /data/datapump 

Open the sqlplus utility and fire the below command to create database directory ( test_dir ) point to os level directory( /data/datapump ).

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

 GRANT READ, WRITE ON DIRECTORY test_dir TO sys; 

Table level backup commad.

 expdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=C##TESTUSER_testtable.log 

Import above table backup with command impdp.

impdp – utility to import the backup of expdp
dumpfile – provide the name of dumpfile. dumpfile name can be taken from expdp command.
logfile – logfile will track the logs of impdp command

 impdp tables=C##TESTUSER.testtable directory=TEST_DIR dumpfile=C##TESTUSER_testtable.dmp logfile=impdp_C##TESTUSER_testtable.log 

Schema backup

Schema backup also take the backup of schema objects.

 expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=C##TESTUSER_USER.log 

Schema Import

 impdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_USER.dmp logfile=imp_C##TESTUSER_USER.log 

Schema include parameter

Schema backup comes with two parameter. these are include and exclude. In following example it will only take the backup of included table and rest of the tables and other objects will be excluded.

 expdp schemas=C##TESTUSER include=TABLE:\""IN ('TESTTABLE2')\"" directory=TEST_DIR dumpfile=C##TESTUSER_include.dmp logfile=C##TESTUSER_include.log 

Schema exclude parameter

Below example will take the backup of schema and all schema objects but will exclude object in excluded list.

 expdp schemas=C##TESTUSER exclude=TABLE:\""IN ('TESTTABLE3')\"" directory=TEST_DIR dumpfile=C##TESTUSER_exclude.dmp logfile=C##TESTUSER_exclude.log 

Following command only takes the metadata information of schema. It is possible with the help of content=METADATA_ONLY parameter.

 expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER _meta.dmp logfile=C##TESTUSER_meta.log content=METADATA_ONLY 

Query Parameter

Query parameter will take the backup of selected rows from tables as per the where condition.

 expdp full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query='SCOTT.EMP:"WHERE deptno=0",SCOTT.DEPT:"WHERE deptno=0"' 

Logtime Parameter

Logtime parameter display the logtime along with the export output. Logitimes comes with three values as below.

• 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_logtime.dmp logfile=C##TESTUSER_logtime.log logtime=all 

Parallel

Parallel will allocate the no of worker to finish the work simultaneously. this parameter is basically usefull to increase the performance of datapump export.
In following example %U refer the name of dump. as we are running job parallelly .It will create the no of dump name serially.
dump file name will be like C##TESTUSER_1.dmp , C##TESTUSER_%2.dmp .

 expdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=C##TESTUSER_parallel.log 

Above dump can be imported like below.

Here in import we have %U as dump is split into no of parts.

 impdp schemas=C##TESTUSER directory=TEST_DIR parallel=4 dumpfile=C##TESTUSER_%U.dmp logfile=impdpC##TESTUSER_parallel.log 

Filesize

We can also limit the size of export dump by using filesize parameter.

 expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G 

Compression

Datapump export can be compress to reduce the size of export dump.

 expdp schemas=C##TESTUSER directory=TEST_DIR dumpfile=C##TESTUSER_filesize_%U parallel=2 filesize=1G compression=all