A week ago, someone had some problems with DBMS_XMLSCHEMA.PURGESCHEMA (available in Oracle 11g). In principle, the problem he had, was that a registered schema could not be deleted via the normal procedures/methods, or at least this was claimed to be the case…
The problem was solved via a very intuitive way (direct delete via a user view), but one I wouldn’t recommend PLUS it also raises some questions…(although I am still not sure how under what circumstances this was doable / which privileges used to make this happen)…
Have a look at the following OTN Forum post, regarding “Purgeschema“. A description of the view user_xml_schemas is shown below as a reference.
SQL> SELECT dbms_metadata.get_ddl('VIEW','USER_XML_SCHEMAS','SYS') FROM dual; DBMS_METADATA.GET_DDL('VIEW','USER_XML_SCHEMAS','SYS') -------------------------------------------------------------------------------- CREATE OR REPLACE FORCE VIEW "SYS"."USER_XML_SCHEMAS" ("SCHEMA_URL", "LOCAL", "SCHEMA", "INT_OBJNAME", "QUAL_SCHEMA_URL", "HIER_TYPE", "BINARY", "SCHEMA_ID", "HIDDEN") AS SELECT s.xmldata.schema_url, CASE WHEN bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16) = 16 THEN 'NO' ELSE 'YES' END, CASE WHEN bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16384) = 16384 THEN xdb.dbms_csx_int.GetCSXSchema(xmltype(VALUE(s).getclobval())) ELSE VALUE(s) END, xdb.xdb$Extname2Intname(s.xmldata.schema_url,s.xmldata.schema_owner), CASE WHEN bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16) = 16 THEN s.xmldata.schema_url ELSE 'http://xmlns.oracle.com/xdb/schemas/' || s.xmldata.schema_owner || '/' || CASE WHEN substr(s.xmldata.schema_url, 1, 7) = 'http://' THEN substr(s.xmldata.schema_url, 8) ELSE s.xmldata.schema_url END END, CASE WHEN bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 128) = 128 THEN 'NONE' ELSE CASE WHEN bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 64) = 64 THEN 'RESMETADATA' ELSE 'CONTENTS' END END, CASE WHEN bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16384) = 16384 THEN 'YES' ELSE 'NO' END, s.sys_nc_oid$, CASE WHEN bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 32768) = 32768 THEN 'YES' ELSE 'NO' END FROM USER$ u, xdb.xdb$schema s WHERE u.name = s.xmldata.schema_owner AND u.USER# = userenv('SCHEMAID') 1 ROW selected. |
2 comments
Marco
27 May, 2009 at 17:26 (UTC 1) Link to this comment
dbms_xmlschema.purgeSchema(schemaid)
Example
SCHEMA_ID used here is the SCHEMA_ID column from the view user_xml_schemas
Marco Gralike
16 February, 2010 at 9:25 (UTC 1) Link to this comment
Be sure that all refering objects will be gone afterwards. The workaround for fantom xml schemas would be