Wednesday, July 23, 2008

ORA-31088: object XYZ depends on the schema

Always check out the original article at for latest comments, fixes and updates.

This error can be seen when you are trying to delete an XML schema from the repository using the DELETE_RESTRICT or DELETE_CASCADE option in DBMS_XMLSCHEMA.DELETE_SCHEMA , but there are still XMLType tables or XMLType columns referencing it.

A slightly subtler situation involves the recycle bin of Oracle 10G and above.
Supposing the recycle bin is enabled, if you drop a table referencing an XML schema but you don't specify PURGE and you don't flush the recycle bin, then you'll get ORA-31088, which may sound difficult to understand at first.

You can reproduce the problem by executing the scripts i used in a previous posting, then performing the following additional steps:

DROP TABLE purchaseorder_as_column;


ORA-31088: object "TEST"."PURCHASEORDER_AS_COLUMN" depends on the schema
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 82
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 102
So, the cleanest workaround is to ensure that no depending objects are still present, including those that may have been moved to the recycle bin and then re-execute deleteSchema.
You may execute the following query, for instance:
select *
from user_dependencies d, user_xml_schemas x
where d.referenced_type = 'XML SCHEMA'
and d.referenced_name = x.int_objname
and x.schema_url = '';

Alternatively you can try to specify either of the two remaining options for the deleteSchema call: DELETE_INVALIDATE or DELETE_CASCADE_FORCE.

See message translations for ORA-31088 and search additional resources.


Marco Gralike said...

An alternative on 11g is using "purgeschema" (

...or the alternative rather strange way off deleting via a view...

Byte64 said...

thanks for the remarks and the links!

I haven't played seriously yet with 11g and even less with built-in packages' new features.

Frankly speaking i'll need to re-read the purgeschema documentation because at a first glance it wasn't so clear for me why oracle had to add a fifth way of deleting a schema...


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles