Suhas Talekar- Business Professional

Datapump is a server based bulk data movement infrastructure that supersedes the import and export utilities for Oracle Database. The old export/ import tools are still available, but do not support all Oracle 10g and 11g features. This new utilities are well known as expdp and impdp.

Here’s how you use the commands:

For example, lets export an existing database:

In the Command prompt of the system ( Note, this is the terminal or command prompt and not SQL prompt) :

[codesyntax lang="oracle8" container="none"]

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 31 March, 2012 11:36:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DMPDIR is invalid

[/codesyntax]

Here, we tried to export the database schema scott with password ‘tiger’ the Directory is dmpdir and the export dump file should be named as scott.dmp. However, we see that we landed into Errors ORA 39002, 39070 and 39087.

The directory we mentioned as dmpdir never existed.  We need to tell oracle the actual path to this directory. Now open the SQL Prompt and type in the below code:

[codesyntax lang="oracle8" container="none"]
SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
Directory created.
[/codesyntax]

In the above statement, we stated the actual path on the drive for the directoy we called 'dmpdir'.

Now grant the necessary privileges to the user.
[codesyntax lang="oracle8" container="none"]
SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Grant succeeded.
[/codesyntax]

Now, we can try the export command again. At the Command prompt :

 


[codesyntax lang="oracle8" container="none"]

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 31 March, 2012 11:41:02
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********  DIRECTORY=dmpdir DUMPFILE=scott.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 175.2 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
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/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported "SCOTT"."BIGEMP"                            145.2 MB 3670016 rows
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."ORD_CHARGE_TAB"                    5.296 KB       2 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."NEWOBJECT1_T"                          0 KB       0 rows
. . exported "SCOTT"."T1"                                    0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /app/oracle/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:44:50

[/codesyntax]

 

Now you can find the exported dump file at the path you specified. :)

Similarly , and import command can be used as below:

 

[codesyntax lang="oracle8" container="none"]

$ impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp

Import: Release 10.2.0.1.0 - 64bit Production on Friday, 31 March, 2012 12:00:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/******** DIRECTORY=dmpdir DUMPFILE=scott.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."BIGEMP" 145.2 MB 3670016 rows
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
. . imported "SCOTT"."ORD_CHARGE_TAB" 5.296 KB 2 rows
. . imported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
. . imported "SCOTT"."NEWOBJECT1_T" 0 KB 0 rows
. . imported "SCOTT"."T1" 0 KB 0 rows
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/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed at 12:02:22

[/codesyntax]

 

Leave a reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>