dbms_xmlschema.PURGESCHEMA feature…

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.

:?