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.

:?

Written by:

2 Comments

  1. 5/27/2009

    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. 2/16/2010

    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.