Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Oracle data pump is new version of imp and exp utilities. It is faster and flexible to the imp and exp utilities used in previous Oracle versions.
This article was originally written against Oracle 10g, but the information is still relevant up to and including the latest versions of Oracle.
Create database directory
First of all you have to create directory because Data Pump is a server-based technology, so it deals with directory objects pointing to physical directories on the database server where to keep the database backup.
1 2 | <span style="color: #000000;">conn / as sysdba; CREATE OR REPLACE DIRECTORY ORA_DIR AS 'F:\Directory';</span> |
Granting Read / Write directory privileges to Users
1 | <span style="color: #000000;">GRANT READ, WRITE ON DIRECTORY ORA_DIR TO HR;</span> |
Database Export / Import
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.
1 2 | <span style="color: #000000;">expdp hr/admin@umis Fully=Y directory= ORA_DIR dumpfile=Fullhr.dmp logfile=ExpdbFullhr_log.log impdp hr/admin@umis Fully =Y directory= ORA_DIR dumpfile= Fullhr.dmp logfile=ImpdbFullhr_log.log</span> |
Schema Export / Import
The “schemas” parameter is used to specify the schemas that are to be exported.
1 2 | <span style="color: #000000;">expdp hr/admin@umis Schemas= hr directory= ORA_DIR dumpfile=hr.dmp logfile=Expdbhr_log.log impdp hr/admin@umis Schemas = hr directory= ORA_DIR dumpfile=hr.dmp logfile=Impdbhr_log.log</span> |
Table Export / Import
The “tables” parameter is used to specify the tables that are to be exported. You can take backup of single or multiple tables with one command.
1 2 | <span style="color: #000000;">expdp hr/admin@umis tables= TRI_RES_STUDENT_SUBJECTMARKS directory= ORA_DIR dumpfile=Tri.dmp logfile=ExpdbTri_log.log impdp hr/admin@umis tables= TRI_RES_STUDENT_SUBJECTMARKS directory= ORA_DIR dumpfile=Tri.dmp logfile=ImpdbTri_log.log</span> |
Include and Exclude
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.
1 2 | <span style="color: #000000;">expdp hr/admin@umis schemas=hr include=TABLE:"IN ('TBL_EMPLOYEE', 'TBL_CANDIDATE')" directory=ORA_DIR dumpfile=Inchr.dmp logfile=Incphr_log.log expdp hr/admin@umis schemas=hr exclude=TABLE:"= 'TBL_VACANT_POST'" directory=ORA_DIR dumpfile=Exchr.dmp logfile=Exchr_log.log</span> |
