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

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.

user4423142

Here the output.

Thanks

SCHEMA_URL	LOCAL	SCHEMA	INT_OBJNAME	QUAL_SCHEMA_URL	HIER_TYPE	BINARY	SCHEMA_ID	HIDDEN
data_formulation.xsd	YES	<?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.xsd	CONTENTS	YES	F4559C7C262E5039E043A1A09384C1D9	YES
data_formulation.xsd	YES	<?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.xsd	CONTENTS	YES	F4589567716D231CE043A1A09384EBAA	YES
data_formulation.xsd	YES	<?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.xsd	CONTENTS	YES	F45895677178231CE043A1A09384EBAA	YES

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.