Knowledge Base : Delete XML Schema hangs

Sometimes, deleting an XML schema in Oracle Database hangs

SQL> BEGIN DBMS_XMLSchema.deleteSchema(schemaurl=>’http://www.xxx.de/XXX/XXX_Schema.xsd’,
delete_option=>DBMS_XMLSchema.Delete_Cascade_Force); END;

First try purgeSchema instead of deleSchema

DBMS_XMLSCHEMA.PURGESCHEMA(
schemaid IN RAW);

If that doesn’t suceedd, you’ll have to identify the blocking session

SQL> select blocking_session from v$session where sid = 154;
BLOCKING_SESSION
—————-
13
SQL> select SERIAL# from V$SESSION WHERE SID = 13;
SERIAL#
———-
801
SQL> alter system kill session ‘13,801’;
System altered.
SQL>