Oracle 12: ORA-00600 12811 when Dropping Table with Identify Column

Product: Oracle RDBMS
Version: 12.1.0.1 - 12.2.0.2

There were many tables in my user which has IDENTITY column, which is a new feature of Oracle 12.1.0.1.  Randomly, Oracle will throw following error for one of them, but the rest has no issue:

ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [391537], [], [], [], [], [], [], [], [], [], []

Searched in Internet, and found that this is a known bug that affected following version:

• 12.1.0.1 - 12.1.0.2
• 12.2.0.1 - 12.2.0.2

The permanent workaround will be to install a patch, and temporary workaround is to drop the identify column first before dropping the table. For example, for table "my_company" with identity column company_ID, it will be:

ALTER TABLE my_company MODIFY company_ID DROP IDENTITY;

drop TABLE my_company;

Above workaround doesn't work in my environment, because my version dropped its internal sequence.  ALTER TABLE MODIFY DROP IDENTITY failed with same error below:

SQL> ALTER TABLE my_company MODIFY company_ID DROP IDENTITY;

ALTER TABLE my_company MODIFY company_ID DROP IDENTITY;

*

ERROR at line 1:

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

In other word, even if I installed the patch, it won't re-create back the sequence, and the table cannot be dropped as well.

Internal of IDENTITY Feature

First, let me list down related components behind IDENTITY column:

1. Table - tabs
2. Column - user_tab_columns
3. Sequence - user_sequences
4. Sequence to Column mapping - user_TAB_IDENTITY_COLS

A new sequence number object with name ISEQ$$_[object_id] will be created for each identity column.  This can be query from user_sequences:

select * from user_sequences where sequence_name = 'ISEQ$$_[object_id]';

In my situation, the entry of the sequence exists in user_tab_columns, but missing in user_sequences and user_tab_identity_cols:

select data_default from user_tab_columns where table_name = 'MY_COMPANY' and column_name = 'COMPANY_ID';

"DH1030"."ISEQ$$_663000".nextval

As you can see, column user_tab_columns.data_default still show the info that there is a sequence called ISEQ$$_663000, but when check in user_sequences, it doesn't exists any more, i.e. it has been dropped:

select count(*) from user_sequences where sequence_name = 'ISEQ$$_663000';

So, it is logical that the sequence to column mapping is missing in user_TAB_IDENTITY_COLS.

Since most of the tables were table to drop, except 1, I believe this is intermittent issue. If I can re-create back following, I could drop the IDENTITY column, then drop the table:

• sequence - a new entry exists in user_sequences, with new ID in user_objects.object_id
• sequence to column mapping - needs to determine the internal system tables that hold the value

Sequence to Column Mapping

Digging into system view user_TAB_IDENTITY_COLS, I found following is the internal table that keep the lookup entry:

1. sys.idnseq$ - it is a table owned by SYS

Above table still show a left over entry of the previous sequence object id (user_objects.object_id) of the table, column, and sequence name:

select * from idnseq$ where obj# = (select object_id from user_objects where object_name = 'MY_COMPANY');

The old sequence object ID stored in sys.idnseq$.seqobj#

So, I need to update this table to point to the new sequence that I manually created.

Workaround

Following are the steps involve to put back the dropped sequence, in order to drop the identity feature and table:

1. Get the old sequence name of identity column my_company.company_id
   select data_default from user_tab_columns where table_name = 'MY_COMPANY' and column_name = 'COMPANY_ID';
   Output: "DH1030"."ISEQ$$_663000".nextval
2. Based on above output, the old sequence name was ISEQ$$_663000
3. Create a new sequence with same name (name doesn't matter)
   create sequence ISEQ$$_663000;
4. Find out the object ID of the new sequence
   select object_id from user_sequences where object_name = 'ISEQ$$_663000';
   Output: 761299
5. Ensure sys.idnseq$.obj# will return 1 row for table my_company
   select count(*) from sys.idnseq$ where obj# = (select object_id from user_objects where object_name = 'MY_COMPANY') and seqobj# = 663000;
6. Update sequence mapping in sys.idnseq$.seqobj# to point to the new sequence number
7. update sys.idnseq$ set seqobj# =761299 where obj# = (select object_id from user_objects where object_name = 'MY_COMPANY') and seqobj# = 663000;
8. Now following system view will show an entry that my_company.company_id maps to sequence ISEQ$$_663000
   select table_name, column_name, generation_type, sequence_name, identity_options from user_TAB_IDENTITY_COLS where table_name = 'MY_COMPANY';
9. Next, it is possible to disable IDENTITY feature for my_company.company_id
   ALTER TABLE my_company MODIFY company_id DROP IDENTITY;
10. Lastly, drop the table
    drop table my_company;

Note: sys.idnseq$.obj# will return 1 row, as only 1 column in a table can has IDENTITY feature, but adding extra check seqobj# = 663000 will eliminate any human mistake