14 November, 2008

Data Pump using default directory

A default "DATA_PUMP_DIR" is available for "privileged users" (Not necessarily having the DBA Role. See the example with a non DBA account here).


SQL> select owner, directory_name, directory_path from dba_directories where directory_name like 'DATA_PU%';

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_PUMP_DIR
/oracle_fs/ora10204/admin/ORT24FS/dpdump/


SQL>

However, since Oracle uses a default filename, it will not overwrite an existing datapump export.


ora10204>expdp hemant/hemant

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 14 November, 2008 23:29:51

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/oracle_fs/ora10204/admin/ORT24FS/dpdump/expdat.dmp"
ORA-27038: created file already exists
Additional information: 1


ora10204>

Thus, the pre-existing dumpfile has to be removed

ora10204>cd $ORACLE_BASE/admin/ORT24FS/dpdump
ora10204>ls -ltr|tail -2
-rw-r----- 1 ora10204 dba 483500032 Nov 13 22:38 expdat.dmp
-rw-r--r-- 1 ora10204 dba 516 Nov 14 23:30 export.log
ora10204>cat export.log
;;;
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 14 November, 2008 23:29:51

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/oracle_fs/ora10204/admin/ORT24FS/dpdump/expdat.dmp"
ORA-27038: created file already exists
Additional information: 1
ora10204>rm expdat.dmp
ora10204>


I am now able to run a datapump export :

ora10204>cd
ora10204>expdp hemant/hemant

Export: Release 10.2.0.4.0 - 64bit Production on Friday, 14 November, 2008 23:32:42

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_SCHEMA_01": hemant/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 534.0 MB
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/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/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HEMANT"."SOURCE_TABLE" 150.9 MB 1620512 rows
. . exported "HEMANT"."TARGET_ASM" 150.9 MB 1620512 rows
. . exported "HEMANT"."TARGET_FS" 150.9 MB 1620512 rows
. . exported "HEMANT"."TARGET_TABLE" 4.721 MB 50612 rows
. . exported "HEMANT"."T_AH_O_G_U" 3.328 MB 105329 rows
. . exported "HEMANT"."TEST_SUM_NUMBERS" 5.265 KB 4 rows
Master table "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_SCHEMA_01 is:
/oracle_fs/ora10204/admin/ORT24FS/dpdump/expdat.dmp
Job "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:33:43

ora10204>
ora10204>cd $ORACLE_BASE/admin/ORT24FS/dpdump
ora10204>ls -ltr|tail -2
-rw-r--r-- 1 ora10204 dba 1948 Nov 14 23:33 export.log
-rw-r----- 1 ora10204 dba 483500032 Nov 14 23:33 expdat.dmp
ora10204>ls -ltr
total 472644
-rw-r--r-- 1 ora10204 dba 116 Jun 14 23:30 dp.log
-rw-r--r-- 1 ora10204 dba 1948 Nov 14 23:33 export.log
-rw-r----- 1 ora10204 dba 483500032 Nov 14 23:33 expdat.dmp
ora10204>
ora10204>cat export.log
;;;
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 14 November, 2008 23:32:42

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_SCHEMA_01": hemant/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 534.0 MB
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/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/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HEMANT"."SOURCE_TABLE" 150.9 MB 1620512 rows
. . exported "HEMANT"."TARGET_ASM" 150.9 MB 1620512 rows
. . exported "HEMANT"."TARGET_FS" 150.9 MB 1620512 rows
. . exported "HEMANT"."TARGET_TABLE" 4.721 MB 50612 rows
. . exported "HEMANT"."T_AH_O_G_U" 3.328 MB 105329 rows
. . exported "HEMANT"."TEST_SUM_NUMBERS" 5.265 KB 4 rows
Master table "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_SCHEMA_01 is:
/oracle_fs/ora10204/admin/ORT24FS/dpdump/expdat.dmp
Job "HEMANT"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:33:43
ora10204>


I now drop some of the tables

ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Nov 14 23:35:41 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: hemant/hemant

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

SQL> drop table source_table purge;
drop t
Table dropped.

SQL>
2
SQL> drop table target_asm purge;

Table dropped.

SQL> drop table target_fs purge;

Table dropped.

SQL> drop table target_table purge;

Table dropped.

SQL> quit


I am now able to import, again using the default DATA_PUMP_DIR and expdat.dmp :


ora10204>impdp hemant/hemant

Import: Release 10.2.0.4.0 - 64bit Production on Friday, 14 November, 2008 23:37:02

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HEMANT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HEMANT"."SYS_IMPORT_FULL_01": hemant/********
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HEMANT" already exists
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/TABLE/TABLE
ORA-39151: Table "HEMANT"."TEST_SUM_NUMBERS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "HEMANT"."T_AH_O_G_U" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HEMANT"."SOURCE_TABLE" 150.9 MB 1620512 rows
. . imported "HEMANT"."TARGET_ASM" 150.9 MB 1620512 rows
. . imported "HEMANT"."TARGET_FS" 150.9 MB 1620512 rows
. . imported "HEMANT"."TARGET_TABLE" 4.721 MB 50612 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HEMANT"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 23:37:48

ora10204>


So, expdp and impdp are easy to use !

2 comments:

Anonymous said...

Hi Hemanth,
It does not happen Usually, I did not faced this problem, but there will be a text file called "dp" which gives the detail that so and so directory got created

- Pavan Kumar N

Hemant K Chitale said...

I don't understand your comment.
What is it that "does not happen" ?
Are you saying that creation of the export dump file does not happen ?
Or are you saying that creation fo a text file called "dp" does not happen ?
Or that the presenec of the "dp" file is the "problem" ?