We encountered this bug on Windows 188.8.131.52.0 Enterprise Edition.
When dropping a table and you get a ORA-00 600 with arguments:  and , you will have to check your application for the following sequence of events:
– You created the table with an identity column GENERATED BY DEFAULT ON NULL AS IDENTITY
– you have code that tries to alter the value the sequence by an alter table statement in dynamic sql.
When the alter table results in an error, the sequence is dropped and you are stuck with a corrupted database.
Don’t do this in a production database, as we have😦
CREATE TABLE edwin_test2 ( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 179193 CACHE 20 NOORDER NOCYCLE CONSTRAINT edw_con2 NOT NULL ENABLE, CREATION_DATE date, CONSTRAINT edwin_test_pk2 PRIMARY KEY (ID) USING INDEX ENABLE ) NO INMEMORY ; declare l_max_id number:= 10; begin insert into edwin_test2 ( creation_date ) values ( sysdate ); commit; -- l_max_id := -1; -- force the error -- -- Let's blow things up... execute immediate 'alter table edwin_test2 modify id number ' ||'generated by default on null as identity ' ||'( start with ' ||l_max_id || ' )'; end; / drop table edwin_test2;
You can check if you’re have this corruption by the following script:
declare cursor c_seq ( cp_name in varchar2 ) is select 'exists' bla from dba_sequences seq where seq.sequence_name = cp_name ; r_seq c_seq%rowtype; begin for r_i in ( select col.table_name , col.data_default as seq from dba_tab_columns col where col.identity_column = 'YES' and not exists ( select 1 from dba_views vw where vw.view_name = col.table_name ) ) loop r_i.seq := substr( r_i.seq, instr(r_i.seq,'.')+2); r_i.seq := substr( r_i.seq, 1, instr(r_i.seq,'"')-1); -- r_seq:= null; open c_seq(r_i.seq); fetch c_seq into r_seq; close c_seq; --- dbms_output.put_line ( rpad(r_i.table_name,40,' ') ||r_i.seq ||' ' ||nvl(r_seq.bla, ' ------ohoh!') ); end loop; end;
To handle this corruption, just open a SR with Oracle. They should be able to help you.
No, recreating the sequence by hand doesn’t work. I tried. The sys.seq.FLAGS column has a value of 8 for a hand-created sequence and 40 for an identity column; I see no problem in selecting from the datadictionairy, but updating it is even for me a big NO.