Oracle Database - Data Pump Import (impdp)

> Database > Oracle Database

1 - About

The import utility of data pump

Advertising

3 - Prerequisites

3.1 - 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.

4 - 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

5 - Example

5.1 - 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.

Advertising

6 - How to

6.1 - get the metadata information of a dmp file

6.1.1 - 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
[[email protected] 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
db/oracle/impdp.txt · Last modified: 2017/09/06 19:30 by gerardnico