3 Replies Latest reply: Mar 12, 2014 11:45 AM by odie_63 RSS

    Delete schema

    user4423142

      Hi everybody,

      It is not the first time, but sometimes it is impossible to delete a schema. I saw information on the Internet and it seems it is a problem with PL/SQL when the schema has been created. The error message is the

      following :

       

      ORA-31000: the resource 'data.xml' is not an XDB schema document

      ORA-06512: "XDB.DBMS_XMLSCHEMA_INT", line 22

      ORA-06512: "XDB.DBMS_XMLSCHEMA", line 106

      ...

      I use SLQ Developper. When I see the schema, 3 lines <xs:schema... are displayed.

      I can create the schema again, it has now 4 lines.

      I can delete the schema, bu now it has 3 lines, and the schema i still displayed.

      If I try to delete again, the message appears.

       

      Thank you for your help

       

      Oracle used : 11g  Release 11.2.0.3.0

        • 1. Re: Delete schema
          odie_63

          Could you post the output of :

          SELECT * FROM user_xml_schemas WHERE schema_url = 'your_schema';

          (properly formatted please)

           

          Do not use SQL Developer object explorer to manage XML schemas in the db.

          Always do it explicitly via DBMS_XMLSCHEMA APIs, where you can control all the options available.

          • 2. Re: Delete schema
            user4423142

            Here the output.

            Thanks

            SCHEMA_URLLOCALSCHEMAINT_OBJNAMEQUAL_SCHEMA_URLHIER_TYPEBINARYSCHEMA_IDHIDDEN
            data_formulation.xsdYES<?xml version="1.0" encoding="utf-8"?>
            <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="2154785" oraxdb:schemaURL="data_formulation.xsd" oraxdb:schemaOwner="ODBA" oraxdb:numProps="5" xmlns:csx="http://xmlns.oracle.com/2004/CSX"><xs:element name="data_formulation" xmlns:csx="http://xmlns.oracle.com/2004/CSX" csx:propertyID="7189" oraxdb:global="true" oraxdb:memType="258"><xs:complexType><xs:annotation><xs:appinfo><csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true"><csx:kid csx:propertyID="7188" kidNum="1"></csx:kid></csx:kidList></xs:appinfo></xs:annotation><xs:sequence><xs:element name="data" minOccurs="0" maxOccurs="unbounded" csx:propertyID="7188" oraxdb:global="false" oraxdb:memType="258" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"><xs:complexType><xs:annotation><xs:appinfo><csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true"><csx:kid csx:propertyID="7185" kidNum="1"></csx:kid><csx:kid csx:propertyID="7186" kidNum="2"></csx:kid><csx:kid csx:propertyID="7187" kidNum="3"></csx:kid></csx:kidList></xs:appinfo></xs:annotation><xs:attribute name="famille" type="xs:string" csx:propertyID="7185" oraxdb:global="false" oraxdb:memType="1"></xs:attribute><xs:attribute name="value" type="xs:string" csx:propertyID="7186" oraxdb:global="false" oraxdb:memType="1"></xs:attribute><xs:attribute name="type" type="xs:string" csx:propertyID="7187" oraxdb:global="false" oraxdb:memType="1"></xs:attribute></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema>
            XD9FWcfCYuUDngQ6Ggk4TB2Q==http://xmlns.oracle.com/xdb/schemas/ODBA/data_formulation.xsdCONTENTSYESF4559C7C262E5039E043A1A09384C1D9YES
            data_formulation.xsdYES<?xml version="1.0" encoding="utf-8"?>
            <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="2154785" oraxdb:schemaURL="data_formulation.xsd" oraxdb:schemaOwner="ODBA" oraxdb:numProps="5" xmlns:csx="http://xmlns.oracle.com/2004/CSX"><xs:element name="data_formulation" xmlns:csx="http://xmlns.oracle.com/2004/CSX" csx:propertyID="7194" oraxdb:global="true" oraxdb:memType="258"><xs:complexType><xs:annotation><xs:appinfo><csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true"><csx:kid csx:propertyID="7193" kidNum="1"></csx:kid></csx:kidList></xs:appinfo></xs:annotation><xs:sequence><xs:element name="data" minOccurs="0" maxOccurs="unbounded" csx:propertyID="7193" oraxdb:global="false" oraxdb:memType="258" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"><xs:complexType><xs:annotation><xs:appinfo><csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true"><csx:kid csx:propertyID="7190" kidNum="1"></csx:kid><csx:kid csx:propertyID="7191" kidNum="2"></csx:kid><csx:kid csx:propertyID="7192" kidNum="3"></csx:kid></csx:kidList></xs:appinfo></xs:annotation><xs:attribute name="famille" type="xs:string" csx:propertyID="7190" oraxdb:global="false" oraxdb:memType="1"></xs:attribute><xs:attribute name="value" type="xs:string" csx:propertyID="7191" oraxdb:global="false" oraxdb:memType="1"></xs:attribute><xs:attribute name="type" type="xs:string" csx:propertyID="7192" oraxdb:global="false" oraxdb:memType="1"></xs:attribute></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema>
            XD9FiVZ3FtIxzgQ6Ggk4Trqg==http://xmlns.oracle.com/xdb/schemas/ODBA/data_formulation.xsdCONTENTSYESF4589567716D231CE043A1A09384EBAAYES
            data_formulation.xsdYES<?xml version="1.0" encoding="utf-8"?>
            <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="2154785" oraxdb:schemaURL="data_formulation.xsd" oraxdb:schemaOwner="ODBA" oraxdb:numProps="5" xmlns:csx="http://xmlns.oracle.com/2004/CSX"><xs:element name="data_formulation" xmlns:csx="http://xmlns.oracle.com/2004/CSX" csx:propertyID="7199" oraxdb:global="true" oraxdb:memType="258"><xs:complexType><xs:annotation><xs:appinfo><csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true"><csx:kid csx:propertyID="7198" kidNum="1"></csx:kid></csx:kidList></xs:appinfo></xs:annotation><xs:sequence><xs:element name="data" minOccurs="0" maxOccurs="unbounded" csx:propertyID="7198" oraxdb:global="false" oraxdb:memType="258" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"><xs:complexType><xs:annotation><xs:appinfo><csx:kidList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" sequential="true"><csx:kid csx:propertyID="7195" kidNum="1"></csx:kid><csx:kid csx:propertyID="7196" kidNum="2"></csx:kid><csx:kid csx:propertyID="7197" kidNum="3"></csx:kid></csx:kidList></xs:appinfo></xs:annotation><xs:attribute name="famille" type="xs:string" csx:propertyID="7195" oraxdb:global="false" oraxdb:memType="1"></xs:attribute><xs:attribute name="value" type="xs:string" csx:propertyID="7196" oraxdb:global="false" oraxdb:memType="1"></xs:attribute><xs:attribute name="type" type="xs:string" csx:propertyID="7197" oraxdb:global="false" oraxdb:memType="1"></xs:attribute></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema>
            XD9FiVZ3F4IxzgQ6Ggk4Trqg==http://xmlns.oracle.com/xdb/schemas/ODBA/data_formulation.xsdCONTENTSYESF45895677178231CE043A1A09384EBAAYES
            • 3. Re: Delete schema
              odie_63

              See the HIDDEN column?

               

              A value of 'YES' (for binary XML) means that the schema has been deleted via DBMS_XMLSCHEMA.deleteSchema, and cannot be referenced anymore.

              It also happens during a schema evolution process.

              I'm not sure why it has been implemented this way.

               

              To remove those rows once and for all, use DBMS_XMLSCHEMA.purgeSchema, passing the SCHEMA_ID.