Basic of Data Pump in Oracle

This post is going to explain about the Oracle Data Pump. We may find n number of articles on Google regards, Oracle DataPump. Let’s add one more. Being Oracle DBA we must explore Oracle RDBMS as much as possible to be veteran in our field. Normal water become Lemonade after adding lemon as we can see the additional feature in RDBMS becomes more useful and valuable.

As we know what is the purpose of Data Pump let’s have a quick review with technical definition.

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later

SQL> CREATE DIRECTORY dpump AS 'D:\Dpump';

SQL> SELECT directory_path FROM all_directories WHERE directory_name = 'DPUMP’;

SQL> CREATE USER Oraclehelp IDENTIFIED BY iloveoraclehelp DEFAULT TABLESPACE Users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;

SQL> GRANT CREATE SESSION, CREATE TABLE TO Oraclehelp;

SQL> GRANT READ, WRITE ON DIRECTORY dpump TO scott;

SQL> GRANT READ, WRITE ON DIRECTORY dpump TO Oraclehelp;

Table Export/Import:

J:\>EXPDP scott/tiger DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=EMP_DEPT.LOG TABLES=emp,dept

Import with Remap Schema

J:\>IMPDP Oraclehelp/iloveoraclehelp DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG TABLES=emp REMAP_SCHEMA=scott:Oraclehelp

Export Metadata Only

J:\>EXPDP scott/tiger DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=EMP_DEPT.LOG TABLES=emp,dept CONTENT=METADATA_ONLY

J:\>IMPDP Oraclehelp/iloveoraclehelp DIRECTORY=dpump DUMPFILE=EMP_DEPT.DMP LOGFILE=EMP_DEPT.LOG TABLES=emp,dept CONTENT=METADATA_ONLY REMAP_SCHEMA=scott:Oraclehelp

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

• ALL loads any data and metadata contained in the source. This is the default.
• DATA_ONLY loads only table row data into existing tables; no database objects are created.
• METADATA_ONLY loads only database object definitions; no table row data is loaded.

Schema Exports/Imports

J:\>EXPDP scott/tiger DIRECTORY=dpump DUMPFILE=SCOTT.DMP LOGFILE=SCOTT.LOG SCHEMAS=scott

Remap Schema

J:\>IMPDP Oraclehelp/iloveoraclehelp DIRECTORY=dpump DUMPFILE=SCOTT.DMP LOGFILE=IMPSCOTT.LOG SCHEMAS=scott REMAP_SCHEMA=scott:Oraclehelp

Remap Tablespace

J:\>IMPDP Oraclehelp/iloveoraclehelp DIRECTORY=dpump DUMPFILE=SCOTT.DMP LOGFILE=IMPSCOTT.LOG SCHEMAS=scott REMAP_TABLESPACE=USER1:USER3 REMAP_TABLESPACE=USER2:USER4

Database Exports/Imports

J:\>EXPDP system/manager DIRECTORY=dpump DUMPFILE=DATABASE.DMP LOGFILE=DATABASE.LOG FULL=Y

J:\>IMPDP system/sys DIRECTORY=dpump DUMPFILE=DATABASE.DMP LOGFILE=DATABASE.LOG FULL=Y

Tablespace Exports/Imports

J:\>EXPDP 'sys/sys as sysdba' DIRECTORY= dpump DUMPFILE=TBSUSERS.DMP LOGFILE=TBSUSER.LOG TABLESPACES=USERS

J:\>IMPDP 'Oraclehelp/iloveoraclehelp' DIRECTORY= dpump DUMPFILE=TBSUSERS.DMP LOGFILE=TBSUSER.LOG TABLESPACES=USERS TABLE_EXISTS_ACTION=REPLACE REMAP_SCHEMA=scott:Oraclehelp

The TABLE_EXISTS_ACTION parameter for Data Pump impdp provides four options:

• SKIPis the default: A table is skipped if it already exists.
• APPEND will append rows if the target table’s geometry is compatible. This is the default when the user specifies CONTENT=DATA_ONLY.
• TRUNCATEwill truncates the table, and then load rows from the source if the geometries are compatible and truncation is possible. For example, it is not possible to truncate a table if it is the target of referential constraints.
• REPLACEwill drops the existing table, then create and load it from the source.

Thanks for giving valuable time to add new gems to Oracle’s treasure.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp