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:
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
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" ?
Post a Comment