Blog
Oracle datapump utility -EXPDP IMPDP
- June 7, 2019
- Posted by: Ankush Thavali
- Category: Oracle DBA
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
- Entire database
- Schema level
- Table level
- Tablespace level
- Transportable tablespace level
Use of Datapump
- Moving data from one schema to another
- Moving data from one version of Oracle to another
- Moving data from one OS to another
- 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
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
- Entire database
- Schema level
- Table level
- Tablespace level
- Transportable tablespace level
Use of Datapump
- Moving data from one schema to another
- Moving data from one version of Oracle to another
- Moving data from one OS to another
- 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