Full Export DB Hang Plus EXP-00056 & OCI-21560
I’ve faced situation which export process going hang. It occurs in Oracle 9i (9.2.0.1) database on Windows Server 2000. No matter either I’m using “exp” utility or exp command or par file, the export process likely to going hang itself like this:
Export: Release 9.2.0.1.0 - Production on Thu Jul 28 16:24:34 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
It stuck at “…exporting object type definitions” forever and ever. Duuhhh..
Workaround I’ve done:
[+] Patch the database using CPU to 9.2.0.8 but no luck, instead I got 3 INVALID components even after I ran the catpatch script 5 times, the export process still hang. The 3 invalid components were:
Oracle Data Mining, Oracle Ultra Search and Oracle XML Database
[+] Ran catalog, catproc, utlrp and rerun the catpatch (The scripts located under %ORACLE_HOME%\rdbms\admin\). The result was OK and I am quite happy because the export process no longer hang. But new error came out at the end of the export process:
.
.
.
. exporting synonyms
. exporting views
EXP-00056: ORACLE error 21560 encountered
OCI-21560: argument 7 is null, invalid, or out of range
EXP-00056: ORACLE error 21560 encountered
OCI-21560: argument 7 is null, invalid, or out of range
EXP-00000: Export terminated unsuccessfully
Seems like the problem came from any invalid views or broken or missing views or anything related to views. After investigate the export log row by row, the problem came from one of the table inside ODM schema.
For me there are 2 options to resolve the problem:
1. Export by schema (since the problem only affected the full export process.
2. Drop the ODM schema
