Data Pump Export (expdp) and Data Pump Import(impdp)This is a featured page

Oracle introduced Data Pump in Oracle Database 10g Release 1. This new oracle technology enables very high transfer of data from one database to another. The oracle Data Pump provides two utilities namely:
  • Data Pump Export which is invoked with the expdp command.
  • Data Pump Import which is invoked with the impdp command.

The above two utilities have similar look and feel with the pre-Oracle 10g import and export utilities (e.g., imp and exp, respectively) but are completely separate. Meaning dump files generated by the original export utility (exp) cannot be imported by the new data pump import utility (impdp) and vice-versa.
Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped a file system directory be specified in the invocation of the data pump import or export.
It for this reason and for convenience that a directory object be created before using the data pump export or import utilities.
For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:
SQL> create directory expdp_dir as '/u01/backup/exports'
then grant read and write permissions to the users who will be performing the data pump export and import.
SQL> grant read,write on directory dpexp_dir to system, user1, user2, user3;
Invoking Data Pump Export
Full Export Mode You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line. A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role.Shown below is an example
$ expdp system/<password> DIRECTORY=exp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og
Schema Export Mode The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go. Optionally, you can include the system privilege grants as well.
$ expdp hr/hr DIRECTORY=exp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe

Table Export Mode This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own schema. You can only specify tables in the same schema.
$ expdp hr/hr DIRECTORY=exp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments

Invoking Data Pump Import The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.
Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database. However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.
$ impdp system/<password> DIRECTORY=exp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og
Schema Import Mode The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.
$ impdp hr/hr DIRECTORY=exp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe

Table Import Mode This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.
$ impdp hr/hr DIRECTORY=exp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments


jonah.harris
jonah.harris
Latest page update: made by jonah.harris , Nov 26 2007, 10:31 PM EST (about this update About This Update jonah.harris Moved from: Database - jonah.harris

No content added or deleted.

- complete history)
More Info: links to this page
Started By Thread Subject Replies Last Post
dbsoracle Import SQLServer table that has an XML datatype 0 Dec 31 2008, 1:50 PM EST by dbsoracle
Thread started: Dec 31 2008, 1:50 PM EST  Watch
Hello:
Does anyone know how to import into an Oracle table, a SQLserver table that has one of its columns a XML dataype.
Thanks much,
Don
Do you find this valuable?    
Keyword tags: import+sqlserver+xml
Moles Data Pump Security 2 Jan 28 2008, 3:42 PM EST by Moles
Thread started: Jan 23 2008, 3:30 PM EST  Watch
It appears that when invoking impdp or expdp there is no choice but to supply both username and password on the command line.

This is a gross security violation in an environment where non-privileged users have any kind of shell access to a (UNIX) server.

Oracle Corp. should know better - shame on you!
22  out of 50 found this valuable. Do you?    
Show Last Reply
Showing 2 of 2 threads for this page

Related Content

  (what's this?Related ContentThanks to keyword tags, links to related pages and threads are added to the bottom of your pages. Up to 15 links are shown, determined by matching tags and by how recently the content was updated; keeping the most current at the top. Share your feedback on Wetpaint Central.)