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;
/