Oracle Data Pump

Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise Manager interface.

1. Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.

2. Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.

3. Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.

4. Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).

Data Pump Export (expdp) :-

For this example, once your export your database before that you must be give privilege on this user. If you need to export you can give " EXP_FULL_DATABASE " and if you need import you can give " IMP_FULL_DATABASE "

SQL> CONNECT SYS/BABU@KEYSTONE AS SYSDBA
Connected.

SQL> GRANT CREATE ANY DIRECTORY TO ORTHONOVC16;

Grant succeeded.

SQL> CREATE OR REPLACE DIRECTORY OTHOC16 AS 'D:\ORTHOC16';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY OTHOC16 TO ORTHONOVC16;

Grant succeeded.

SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ORTHONOVC16;

Grant succeeded.

Table level Export :-

SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE tables=G_USER DIRECTORY=OTHOC16 DUMPFILE=ORTHO_G_USER.DMP LOGFILE=ORTHOLOG.LOG


The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema level export :-

SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE SCHEMAS=ORTHONOVC16 DIRECTORY=OTHOC16 DUMPFILE=ORTHONOVC16.DMP LOGFILE=ORTHONOVC16.LOG
DataBase level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE FULL=Y DIRECTORY=OTHOC16 DUMPFILE=DBORTHO.DMP LOGFILE=DBORTHO.LOG
Other export's :

Exclude = View, Proceudre, Function , Constraint , Index

Include = Table: " in ( 'emp') "

Content = ALL (by default ) / data_only / metadata_only

Estimate_Only = Before export your dumpfile you can estimate your dumpfile size using the bellow

parameter " ESTIMATE_ONLY = Y "