CODEWITHSUNDEEP
oracle-databaseoracle12c
mdemir
mdemir

Reputation: 112

Dropping table identity causes ORA-00600 error on Oracle 12c

My Oracle DB version is 12.1.0.2.0.

I'm having a hard time removing the column identity. Also tried to drop the column and dropping the table with purge command, but every time I'm getting the same Oracle error:

ORA-00600: internal error code, arguments: [12811], [96650], [], [], [], [], [], [], [], [], [], []

Just can't touch the identity column. I tried below commands but no luck:

ALTER TABLE DYS_CATEGORY MODIFY CATEGORY_ID DROP IDENTITY;

ALTER TABLE DYS_CATEGORY DROP COLUMN CATEGORY_ID;

DROP TABLE DYS_CATEGORY PURGE;

I can drop any other column from the table, but the problem is with identity column.

Identity columns are new to Oracle, just introduced in 12c.

Upvotes: 4

Views: 5463

Answers (3)

Lahu rathod
Lahu rathod

Reputation: 1

after lots of search and hard work if the table stil showing error ORA-00600: internal error code, arguments:

Do the below step.

  1. Take a backup of original tables syntax: Create table original_table_back as select * from original_table;

  2. Rename original table to some new table name syntax: Rename original_table to original_table_1;

  3. Rename backup to the original table syntax: Rename Original_table_back to original_table.

Upvotes: 0

Stephen Agan
Stephen Agan

Reputation: 11

As William said above it looks like there is/was a system generated the sequence for the identity column that was deleted but the record in idnseq$ remains intact.

I would not recommend this to anyone, but I created a new sequence called junk in the same schema. I then found the object_id for the table and the sequence I created and updated idnseq$ manually changing the seqobj# to the object_id of my new sequence for the object# of the table in question.

I was then able to drop the table and purge the recyclebin successfully.

Really don't recommend hacking oracle system tables, but this was a test system that didn't really matter and it worked.

Upvotes: 1

APC
APC

Reputation: 146249

This is a problem with Oracle 12.1.0.2.0. At least one other person has reported it (on Windows, which may be relevant).

The error you have is an ORA-00600, which is Oracle's default message for unhandled exceptions i.e. Oracle bugs. The correct answer is to raise a Service Request with Oracle Support; they will be able to provide you with a patch or a workaround if you have a corrupted table you need to fix. If you don't have a Support contract you may be out of luck.

For future reference dropping identity columns is a two-stage process:

alter table t42 modify id drop identity;

alter table t42 drop column id;

As it happens, this is not a problem on the very latest version of the product. In Oracle 18c we can just drop the column without modifying it first. LiveSQL demo.

Upvotes: 1

Related Questions