Is here anyway to make a table valid again if it has an XMLType column with an XMLSchema associated it which has been deleted via dbms_xmlschema.deleteSchema with a DELETE_INVALIDATE parameter? I cannot even successfully drop the XMLType column from the table afterwards to get at the other column data.
Here's a simple example to get a table in the invalid state.
declare
doc varchar2(3000) := '
<xs:schema xmlns:xs="
http://www.w3.org/2001/XMLSchema" xmlns:xdb="
http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="field">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="value" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';
begin
dbms_xmlschema.registerSchema(
'
http://www.something.com/XMLSCHEMA/mvf_string_temp.xsd', doc, FALSE);
end;
/
create table fdd.schematest (
idx_id integer not null,
mvf_string XMLType
)
xmltype column mvf_string
XMLSCHEMA "
http://www.something.com/XMLSCHEMA/mvf_string_temp.xsd" element "field"
tablespace fcb
storage
(
initial 10k
next 10k
)
;
/
begin
dbms_xmlschema.deleteSchema(
'
http://www.something.com/XMLSCHEMA/mvf_string_temp.xsd', dbms_xmlschema.DELETE_INVALIDATE);
end;
/
select idx_id from fdd.schematest;
/