Imp / Exp

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.


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.Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.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 "