XML DB: Forced XSD Schema deregistering

I played with OracleDB 11.1.0.7 XML DB and ended up in the situation where I couldn’t get my registered schema to disappear from Enterprise manager console.

Tried with all the options in the deletion mode, still the schema appears in the schema list and I get ORA-31000 errors while trying to delete the schema both from EM console and SQL*Plus:

SQL> exec DBMS_XMLSCHEMA.DELETESCHEMA('http://www.oracle.com/emp.xsd', DBMS_XMLS
CHEMA.DELETE_CASCADE_FORCE);
BEGIN DBMS_XMLSCHEMA.DELETESCHEMA('http://www.oracle.com/emp.xsd', DBMS_XMLSCHEM
A.DELETE_CASCADE_FORCE); END;

*
ERROR at line 1:
ORA-31000: Resource 'http://www.oracle.com/emp.xsd' is not an XDB schema
document
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 106
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 102
ORA-06512: at line 1

Purging Schema

The solution for this is purging the schema. First of all, take a look at the registered schemas using SQL statement:

SQL> select schema_url from user_xml_schemas;

SCHEMA_URL
---------------------------------------------

http://www.oracle.com/emp.xsd

SQL>

select schema_id from user_xml_schemas;

SCHEMA_ID
--------------------------------
91429F33C64A4D60B16294C670DD86C7

Now, copy that schema ID as the parameter to PURGESCHEMA and execute following:

SQL> exec DBMS_XMLSCHEMA.PURGESCHEMA('91429F33C64A4D60B16294C670DD86C7');

PL/SQL procedure successfully completed.

After this you shouldn’t have that extra schema hanging in your schema list anymore.