Oracle Database - Data Pump Import (impdp)

Card Puncher Data Processing

About

The import utility of data pump

Data Pump Sqldeveloper

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





Discover More
Data Pump Sqldeveloper
Oracle Database - Data Pump - (Export|Import) Utilities

Oracle Data Pump works with the file system of the database whereas Export and Import utilities works with the client file system. Since Oracle Database 10g, Oracle Data Pump enables movement of data...



Share this page:
Follow us:
Task Runner