Tuesday 21 June 2011

How to fix expdp errors ORA-39095 and ORA-01466 ?

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>

3 comments:

  1. Thanks a lot for another wonderful write – up, interesting details you have remarked – great info!

    www.n8fan.net

    ReplyDelete
  2. You should participate in a contest for among the finest blogs on the web. I will suggest this website! bovada casino

    ReplyDelete