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           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 --@?/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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s