Database.fi

Professional Database Services Oy

Using expdp and impdp export and import in 10g and 11g

Posted by pparkko on May 16, 2011

Just a quick example of expdp and impdp.

First, check that you have the OS directory like /temp or c:\temp.

Since this example was made in Windows world, we use c\temp.

Log in and create an Oracle Directory:

C:\Temp>sqlplus system/oracle

SQL*Plus: Release 11.2.0.2.0 Beta on Ma Touko 16 11:05:13 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

SQL> create directory dmpdir AS 'c:\temp';

Directory created.

You may have a suitable Oracle Directory already, you can check it from DBA_DIRECTORIES:

SQL> SELECT directory_name, directory_path FROM dba_directories ;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
-------------------------------------------------------------------

DMPDIR
c:\temp

All right, we have what we need.

Let’s say that we need to export HR without knowing HR user’s password.

From OS level:

C:\Temp>expdp system/oracle dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=
hr_exp.log

Export: Release 11.2.0.2.0 - Beta on Ma Touko 16 11:29:37 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** dumpfile=hr.dmp direc
tory=dmpdir schemas=hr logfile=hr_exp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
C:\TEMP\HR.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 11:32:55

C:\Temp>dir

16.05.2011 11:30 .
16.05.2011 11:30 ..
16.05.2011 11:32 503 808 HR.DMP
16.05.2011 11:32 2 395 hr_exp.log

Import is very simple.
expdp / impdp is somewhat easier to use than exp/imp, since most of the times you can use the same parameters while exporting and importing.

Basically I changed EXPDP => IMPDP and the name of the logfile.

Impdp command:
impdp system/oracle dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=hr_imp.log

What is really great about expdp & impdp is that you can export and import to new schemas very easily. That needed some work earlier with export and import.

I wrote another entry about that, just click here to see it.

Add A Comment