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.

šŸ˜•

Marco Gralike Written by:

2 Comments

  1. May 27

    dbms_xmlschema.purgeSchema(schemaid)

    Example

     call dbms_xmlschema.purgeSchema('6AE68CDD7D760C09E040FC0A66FC6882');

    SCHEMA_ID used here is the SCHEMA_ID column from the view user_xml_schemas

  2. February 16

    Be sure that all refering objects will be gone afterwards. The workaround for fantom xml schemas would be

    select x.xmldata.schema_url 
    from   xdb.xdb$schema x 
    where  x.xmldata.schema_owner=user
      and  x.xmldata.schema_url='phantom_schema_url.xsd'
    ;
    
    XMLDATA.SCHEMA_URL
    -----------------------
    phantom_schema_url.xsd
    phantom_schema_url.xsd
    phantom_schema_url.xsd
    phantom_schema_url.xsd
    
    4 rows selected
    
    delete from xdb.xdb$schema x
    where x.xmldata.schema_owner=user
      and x.xmldata.schema_url='phantom_schema_url.xsd'
    ;
    
    4 rows deleted.
    
    commit;
    

Comments are closed.