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

Leave a Reply

Your email address will not be published. Required fields are marked *