CODEWITHSUNDEEP
sqloracle-databaseddl
JohnHC
JohnHC

Reputation: 11195

User defined type dropped while in use

I have an Oracle database with a table as follows:

create table Table1
(Column1 number(5,0),
 Column2 special_type);

Now, due to some data errors, the support team decided the fix would be to drop and recreate the Type.

I now have a table as follows: 

Table1
Column1 number(5,0),
Column2 null

The problem is, I cannot drop the table, I get a "Table has errors" message. I cannot alter the table, I get a "Table has errors" message. I have tried to manipulate the DDL in Oracle SQL Developer, guess what I get? A "Table has errors" message.

Can anyone point me in the right direction?

Upvotes: 1

Views: 87

Answers (2)

XING
XING

Reputation: 9886

It seems support team have dropped the type forcefully. In normal scenario, you cannot drop a type if it has any dependent table.

See demo:

SQL> CREATE OR REPLACE TYPE NUU AS TABLE OF NUMBER;
    /

Type created.

SQL> CREATE TABLE TBLLL(NUM NUU)
NESTED TABLE NUM STORE AS VVVVV ;
/
Table created.

Now when i try to do a simple drop type, i get the below error:

SQL> drop  type nuu;
drop  type nuu
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

So i drop it forcefully:

SQL> drop  type nuu force;

Type dropped.

And when i try to make a select i get the error:

SQL> select * from tblll;
select * from tblll
              *
ERROR at line 1:
ORA-04063: table "USER.TBLLL" has errors

So in order to Alter the table you first need to create the type back. Once the type is created back, your table definition becomes correct and then you can Alter your table.

Other solution is to drop the table and recreate it which you already mentioned is not working.

Upvotes: 1

Shruti Joshi
Shruti Joshi

Reputation: 173

You can try the following -

  1. re-create the type

  2. create another table with same table structure

  3. insert the data into the second table

use the second table now.

You may want to not recreate the type since it caused issues earlier. Find an alternative. It might solve your issue.

Upvotes: 0

Related Questions