Search My Oracle Blog

Custom Search

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" ?

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016