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