Oracle Database - Data Pump

About

Oracle Data Pump is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another.

Articles Related

Data Pump

Export

In order to use Data Pump, the database administrator must create a directory object and grant privileges to the user on that directory object. If a directory object is not specified, a default directory object called data_pump_dir is provided. The default data_pump_dir is available only to privileged users unless access is granted by the DBA.

-- create a directory
SQL> CREATE directory data_dump_dir AS 'F:\Dumps';
 
Directory created.
 
SQL> GRANT READ,WRITE ON DIRECTORY data_dump_dir TO QS_ODS, QS_STG;
 
GRANT succeeded.
 
SQL> exit
Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.3.0 - Production
WITH the Partitioning, OLAP AND DATA Mining options
 
-- the user QS_ODS can now export his database objects with command arguments:
C:\Documents AND Settings\ngerard>expdp QS_ODS/QS2007 DIRECTORY=data_dump_dir dumpfile=QS_ODS.dmp
 
Export: Release 10.2.0.3.0 - Production ON Donderdag, 16 April, 2009 4:56:35
 
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.
 
Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.3.0 - Production
WITH the Partitioning, OLAP AND DATA Mining options
Starting "QS_ODS"."SYS_EXPORT_SCHEMA_01":  QS_ODS******** DIRECTORY=data_dump_dir dumpfile=QS_ODS.dmp
Estimate IN progress USING BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation USING BLOCKS method: 813.2 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
.......

Import

impdp username/password DIRECTORY=dpump_dir1 DUMPFILE=scott.dmp REMAP_SCHEMA=scott:jim

Note how the FROMUSER/TOUSER syntax of the old imp is replaced by the REMAP_SCHEMA option.

Options	Syntax	Description
REMAP_TABLESPACE	REMAP_TABLESPACE=source_tablespace:target_tablespace	Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.
REMAP_SCHEMA	REMAP_SCHEMA='source_schema':'target_schema'	Loads all objects from the source schema into a target schema.
SCHEMAS	SCHEMAS=schema_name [,…]	Specifies that a schema-mode import is to be performed

impdp "'"sys/pwd as sysdba"'" 
REMAP_TABLESPACE='USERS':'SAMPLE' 
REMAP_SCHEMA=BISAMPLE:DI_BISAMPLE,BIFOD:DI_BIFOD
directory=datapumpdir 
dumpfile=SASchemas.dmp 
version=11.2.0.2.0 
schemas=BISAMPLE,BIFOD
LOGFILE=SASchemas_imp.log