11gR2DATAPUMPEXPDPIMPDPOracle

ORA-38500: Unsupported operation: Oracle XML DB not present

While trying to import using impdp got the below error

Table "ANAND"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "ANAND"."TEST":"Y2012_Q2_M06" failed to load/unload and is being skipped due to error:
ORA-38500: Unsupported operation: Oracle XML DB not present
..............

As it said XML db nor present, check for XDB status in dba_registry

COMP_NAME                            VERSION      STATUS
------------------------------------ ------------ --------
Oracle XML Database                  11.2.0.3.0   VALID
03:15:37 SYS > select owner, object_name, object_type, status from dba_objects where status = 'INVALID' and owner = 'XDB';
no rows selected

As per DOC ID 1424643.1, the error can be generated when tables metadata of the exported table in the dumpfile and the existing table at the target DB are different.On checking the table structure there wasn’t any difference.
After spending some more time on it, finally decided to deinstall/install XDB.

Deinstall -- @?/rdbms/admin/catnoqm.sql
Install [email protected]?/rdbms/admin/catqm.sql {XDB pwd} {XDB default tbs} {XDB temporary tbs} {SecureFiles = YES/NO}

For more details one can refer to Doc id 1292089.1
After this, impdp completed successfully. πŸ™‚

Leave a Reply