About
The import utility of data pump
Articles Related
Prerequisites
Directory
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.
Syntax
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
| 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 |
Example
Remap
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.
How to
get the metadata information of a dmp file
sqlfile
impdp can export the DDL of a dmp backup with the sqlfile option.
For example:
impdp '/ as sysdba' dumpfile=myDumpFile.dmp logfile=myImportLogFile.txt sqlfile=myDdlDump.txt
[oracle@Exalytics-01 oracle]$ impdp "'"/ as sysdba"'" directory=DATA_PUMP_DIR_airline dumpfile=airlines.dmp logfile=airlines_import_log.txt sqlfile=airlines_ddl_dump.txt
Import: Release 11.2.0.3.0 - Production on Tue May 15 11:54:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR_airline dumpfile=airlines.dmp logfile=airlines_import_log.txt sqlfile=airlines_ddl_dump.txt
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 11:55:02