Oracle Data Pump (expdp) schema export fails with ORA-01801

November 29, 2011 by 2 Comments 

Platform

Oracle Database Server 10gR2 (10.2.0.5)

Problem

Data Pump (expdp) schema export fails on Oracle 10gR2 with following Oracle error:

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_STATISTICS]
ORA-01801: date format is too long for internal buffer
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6313

----- PL/SQL Call Stack -----
object      line  object
handle    number  name
c00000003abc6b30     15032  package body SYS.KUPW$WORKER
c00000003abc6b30      6372  package body SYS.KUPW$WORKER
c00000003abc6b30      2396  package body SYS.KUPW$WORKER
c00000003abc6b30      6944  package body SYS.KUPW$WORKER
c00000003abc6b30      1314  package body SYS.KUPW$WORKER
c00000003c104720         2  anonymous block

Cause

It seems that there is some invalid date in dictionary tables that is related to database index statistics. However the problem still exists with EXCLUDE=STATISTICS. It’s strange that sometimes the export works fine and sometimes not throwing an Oracle error. It might be that the affected indexes are Oracle Text Search indexes that are periodically recreated.

Solution

For me this Oracle error looks like an Oracle bug, but bug fix is not available from Oracle since the issue is considered as data problem. However a few workarounds can be used to bypass this problem. See below:

a)      Use EXCLUDE=INDEX data pump parameter. It will avoid the Oracle error but excludes indexes from the Data Pump export (expdp).

b)      Use the original Oracle Export (exp) utility

c)      And the most accurate solution. Find the indexes affected by the problem running the following PL/SQL statement:

set serverout on
exec dbms_output.enable(100000);
declare
c varchar2(20);
begin
for r in (select c.name OWNER, b.name object_name, a.rowid row_id
from sys.ind$ a, sys.obj$ b, sys.user$ c
where b.obj# = a.obj# and c.user# = b.owner# and c.name='SCHEMA NAME') loop
begin
select analyzetime
INTO c
from sys.ind$
where rowid = r.row_id;
exception
when others then
dbms_output.put_line ('ANALYZETIME: Error on rowid ' ||R.row_id||' Owner '||r.OWNER||'
Objects '||r.object_name);
dbms_output.put_line (sqlerrm);
end;
end loop;
end;
/

---Possible output ------------
ANALYZETIME: Error on rowid AAAAACAABAAAKglAAG Owner SCHEMA NAME
Objects
<INDEX NAME>
ORA-01801: date format is too long for internal buffer

After that, regarther database statistics for the above reported indexes and rerun Data Pump export (expdp):

EXECUTE DBMS_STATS.DELETE_INDEX_STATS('SCHEMA NAME','INDEX NAME’);
EXECUTE DBMS_STATS.DELETE_INDEX_STATS('SCHEMA NAME','INDEX NAME’);

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

2 Responses to “Oracle Data Pump (expdp) schema export fails with ORA-01801”
  1. My problem solved…

    exec dbms_stats.delete_database_stats;
    exec dbms_stats.gather_database_stats;

    /Ingemar

  2. Kirill Loifman says:

    This should work but deleting stats for the entire DB is very risky since this might change considerably the optimizer behaviour and response times for many SQL statements. So better use the script above to identify the actual problematic objects and delete stats only for them.

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email