Below errors are fixed to make a correct expdp code :
ORA-39095: Dump file space has been exhausted
Sol: By increasing the filesize
ORA-01466: unable to read data - table definition has changed
Sol: Don't mention flashback_time parameter in export.
OR
At OS level, command prompt, do below before export :
F:\export>set nls_date_format=DD-MM-YY HH24:MI:SS
F:\export>set nls_date_format=DD-MM-YY HH24:MI:SS
F:\export>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 21 02:30:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn u1/u1
Connected.
SQL> select max(LAST_DDL_TIME) from tab2;
MAX(LAST_DDL_TIME
-----------------
21-06-11 01:21:12
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
F:\export>del *.dmp
F:\export>expdp u1/u1@new1 parfile=exp.par
Export: Release 10.2.0.1.0 - Production on Tuesday, 21 June, 2011 2:30:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "U1"."SYS_EXPORT_SCHEMA_04": u1/********@new1 parfile=exp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.937 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
. . exported "U1"."TAB2" 835.5 KB 9518 rows
. . exported "U1"."SYS_EXPORT_SCHEMA_03" 214.8 KB 1091 rows
. . exported "U1"."SYS_EXPORT_SCHEMA_02" 172.7 KB 1092 rows
. . exported "U1"."SYS_EXPORT_SCHEMA_01" 127.3 KB 1059 rows
. . exported "U1"."TAB1" 9.039 KB 8 rows
Master table "U1"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_SCHEMA_04 is:
F:\EXPORT\U101.DMP
F:\EXPORT\U102.DMP
F:\EXPORT\U103.DMP
F:\EXPORT\U104.DMP
Job "U1"."SYS_EXPORT_SCHEMA_04" successfully completed at 02:31:09
F:\export>del *.dmp
F:\export>type exp.par
# On CMD
# ======
# Check where your datapump directory is pointing, check dba_directories.
directory=datapump schemas=u1 FLASHBACK_TIME="TO_TIMESTAMP(SYSDATE, 'DD-MM-YYYY HH24:MI:SS')" dumpfile=u1%U.dmp logfile=u1.log filesize=500k
# Run this as expdp system/manager@new1 parfile=exp.par
F:\export>echo %nls_date_format%
DD-MM-YY HH24:MI:SS
F:\export>
ORA-39095: Dump file space has been exhausted
Sol: By increasing the filesize
ORA-01466: unable to read data - table definition has changed
Sol: Don't mention flashback_time parameter in export.
OR
At OS level, command prompt, do below before export :
F:\export>set nls_date_format=DD-MM-YY HH24:MI:SS
F:\export>set nls_date_format=DD-MM-YY HH24:MI:SS
F:\export>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 21 02:30:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn u1/u1
Connected.
SQL> select max(LAST_DDL_TIME) from tab2;
MAX(LAST_DDL_TIME
-----------------
21-06-11 01:21:12
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
F:\export>del *.dmp
F:\export>expdp u1/u1@new1 parfile=exp.par
Export: Release 10.2.0.1.0 - Production on Tuesday, 21 June, 2011 2:30:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "U1"."SYS_EXPORT_SCHEMA_04": u1/********@new1 parfile=exp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.937 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
. . exported "U1"."TAB2" 835.5 KB 9518 rows
. . exported "U1"."SYS_EXPORT_SCHEMA_03" 214.8 KB 1091 rows
. . exported "U1"."SYS_EXPORT_SCHEMA_02" 172.7 KB 1092 rows
. . exported "U1"."SYS_EXPORT_SCHEMA_01" 127.3 KB 1059 rows
. . exported "U1"."TAB1" 9.039 KB 8 rows
Master table "U1"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_SCHEMA_04 is:
F:\EXPORT\U101.DMP
F:\EXPORT\U102.DMP
F:\EXPORT\U103.DMP
F:\EXPORT\U104.DMP
Job "U1"."SYS_EXPORT_SCHEMA_04" successfully completed at 02:31:09
F:\export>del *.dmp
F:\export>type exp.par
# On CMD
# ======
# Check where your datapump directory is pointing, check dba_directories.
directory=datapump schemas=u1 FLASHBACK_TIME="TO_TIMESTAMP(SYSDATE, 'DD-MM-YYYY HH24:MI:SS')" dumpfile=u1%U.dmp logfile=u1.log filesize=500k
# Run this as expdp system/manager@new1 parfile=exp.par
F:\export>echo %nls_date_format%
DD-MM-YY HH24:MI:SS
F:\export>
Thanks a lot for another wonderful write – up, interesting details you have remarked – great info!
ReplyDeletewww.n8fan.net
Thank you !!
ReplyDeleteYou should participate in a contest for among the finest blogs on the web. I will suggest this website! bovada casino
ReplyDelete