Howto drop invalid table?

From: jbl <jarmo.blomsterN0SPM_at_ccc.fi>
Date: Tue, 17 May 2005 08:50:27 GMT
Message-ID: <nfiie.69$PP6.2@read3.inet.fi>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

I've created a table:

CREATE TABLE JBL.TMB (

N_SEQNO NUMBER NOT NULL,
S_SUBJECT VARCHAR2 (4000 CHAR) NOT NULL,
D_DATETIME DATE NOT NULL,
S_SOURCE_SYS VARCHAR2 (4000 CHAR) NOT NULL,
S_SOURCE_VER VARCHAR2 (4000 CHAR) NOT NULL,
S_SOURCE_INST VARCHAR2 (4000 CHAR) NOT NULL,
X_DATA XMLTYPE )

XMLTYPE COLUMN X_DATA STORE AS CLOB
XMLSchema "http://www.xyz.com/xsd/mb-data" ELEMENT "mb-data"
;
/

ALTER TABLE JBL.TMB ADD CONSTRAINT "TMB_PK" PRIMARY KEY ("N_SEQNO") ENABLE;
/

Then I dropped the xml-schema with:
exec dbms_xmlschema.deleteSchema('http://www.xyz.com/xsd/mb-data', dbms_xmlschema.delete_cascade_force);

Now the table is invalid, when I try to drop the table I get:

SQL> drop table tmb;
drop table tmb

           *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-04063: table "JBL.TMB" has errors

How can I now fix, recreate or even drop the table?

TIA, Jarmo Received on Tue May 17 2005 - 03:50:27 CDT