Using expdp and impdp export and import in 10g and 11g
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.