Datapump
From Oracle FAQ
Datapump is a server based bulk data movement infrastructure that supersedes the old import and export utilities. The old export/import tools are still available, but do not support all Oracle 10g, 11g and 12c features. The new utilities are named expdp and impdp.
Contents
Start using datapump export[edit]
$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp Export: Release 10.2.0.1.0 - 64bit Production on Friday, 31 March, 2006 11:36:07 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name DMPDIR is invalid
Oops, we need to create a directory first!
Create database directories[edit]
Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:
SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle'; Directory created.
SQL> GRANT read, write ON DIRECTORY dmpdir TO scott; Grant succeeded.
PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:
SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR'; DIRECTORY_PATH -------------------------------------------------------------------------------- /app/oracle/product/10.2.0/rdbms/log/
Let's try the export again[edit]
$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp Export: Release 10.2.0.1.0 - 64bit Production on Friday, 31 March, 2006 11:41:02 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** DIRECTORY=dmpdir DUMPFILE=scott.dmp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 175.2 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/JOB . . exported "SCOTT"."BIGEMP" 145.2 MB 3670016 rows . . exported "SCOTT"."DEPT" 5.656 KB 4 rows . . exported "SCOTT"."EMP" 7.820 KB 14 rows . . exported "SCOTT"."ORD_CHARGE_TAB" 5.296 KB 2 rows . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows . . exported "SCOTT"."NEWOBJECT1_T" 0 KB 0 rows . . exported "SCOTT"."T1" 0 KB 0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: /app/oracle/scott.dmp Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:44:50
Import into another database[edit]
impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp Import: Release 10.2.0.1.0 - 64bit Production on Friday, 31 March, 2006 12:00:59 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/******** DIRECTORY=dmpdir DUMPFILE=scott.dmp Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."BIGEMP" 145.2 MB 3670016 rows . . imported "SCOTT"."DEPT" 5.656 KB 4 rows . . imported "SCOTT"."EMP" 7.820 KB 14 rows . . imported "SCOTT"."ORD_CHARGE_TAB" 5.296 KB 2 rows . . imported "SCOTT"."SALGRADE" 5.585 KB 5 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows . . imported "SCOTT"."NEWOBJECT1_T" 0 KB 0 rows . . imported "SCOTT"."T1" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/JOB Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed at 12:02:22
Network import[edit]
With network mode imports, one doesn't need any intermediate dump files (GREAT, no more FTP'ing of dump files). Data is exported across a database link and imported directly into the target database. Example:
SQL> create user new_scott identified by tiger; User created. SQL> grant connect, resource to new_scott; Grant succeeded. SQL> grant read, write on directory dmpdir to new_scott; Grant succeeded. SQL> grant create database link to new_scott; Grant succeeded.
SQL> conn new_Scott/tiger Connected. SQL> create database link old_scott connect to scott identified by tiger using 'orcl.oracle.com'; Database link created.
impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott
All work is performed on the target system. The only reference to the source systems is via the database link.
Invoking from PL/SQL[edit]
One can invoke datapump from PL/SQL -- this might be handy for scheduling a daily or weekly export with DBMS_SCHEDULER.
DECLARE
hand NUMBER;
BEGIN
hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL',
job_name => 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.log',
directory => 'DMPDIR',
filetype => 3);
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.dmp',
directory => 'DMPDIR',
filetype => 1);
-- Dbms_DataPump.Set_Parameter(handle => hand,
-- name => 'ESTIMATE',
-- value => 'STATISTICS');
Dbms_DataPump.Start_Job(hand);
END;
/
More info[edit]
The above examples should be enough to get you started. For more into, read the Oracle Utilities Guide.
To list all command line parameters, type:
expdp help=yes impdp help=yes
Utilities[edit]
McDP is a program that does more than expdp and impdp:
32-bit McDP
64-bit McDP
You can post comments in Forum topic: McDP: like expdp/impdp Data Pump programs and much more.
