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.

😕