I recently heard about a bug related to identity columns and their underlying sequence : if the sequence is dropped, an insert in the corresponding identity column would generate an error. Somehow it makes sense, but I wanted to check the behaviour in a recent Oracle Database version.

Fortunately, I have an “Always Free” 18.4 Autonomous Database in the Oracle Cloud, which is very convenient to run this kind of quick tests. The schema I am using is empty.

First, let’s create a simple table :

SQL> create table metal_bands (
id number generated always as identity,
band varchar2(100) not null
);

Table METAL_BANDS created. 

SQL> insert into metal_bands (band) values ('opeth');
1 row inserted.
SQL> insert into metal_bands (band) values ('tool');
1 row inserted.
SQL> commit ;
Commit complete.

SQL> select * from metal_bands ; 

ID     BAND
------ ----------
1      opeth
2      tool

Then, let’s find out if a sequence has been created :

SQL> select SEQUENCE_NAME from user_sequences ;

SEQUENCE_NAME
--------------
ISEQ$$_32528

Views *_TAB_IDENTITY_COLS are helpful to confirm this sequence is related to the newly created metal bands table :

SQL> select TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,SEQUENCE_NAME from user_tab_identity_cols ;

TABLE_NAME    COLUMN_NAME    GENERATION_TYPE    SEQUENCE_NAME
------------- -------------- ------------------ -----------------
METAL_BANDS   ID             ALWAYS             ISEQ$$_32528

Now, let’s try to not-accidentally drop the sequence :

SQL> drop sequence ISEQ$$_32528 ;

ORA-32794: cannot drop a system-generated sequence
32794.0000 - "cannot drop a system-generated sequence"
*Cause: An attempt was made to drop a system-generated sequence.
*Action: A system-generated sequence, such as one created for an
identity column, cannot be dropped. 

This is quite reassuring : it is not possible to drop the sequence, the identity column should not be broken.

What happens if the table is dropped ?

SQL> drop table metal_bands ;

Table METAL_BANDS dropped.

Does the sequence still exist ?

SQL> select TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,SEQUENCE_NAME from user_tab_identity_cols ;

TABLE_NAME                     COLUMN_NAME    GENERATION_TYPE    SEQUENCE_NAME
------------------------------ -------------- ------------------ -----------------
BIN$mG8zqxdsCm7gUzkQAAohOg==$0 ID             ALWAYS             ISEQ$$_32528

Yes, and it is still linked to the dropped table … Let’s try to drop the sequence again :

SQL> drop sequence ISEQ$$_32528 ; 

ORA-32794: cannot drop a system-generated sequence
32794.0000 - "cannot drop a system-generated sequence"
*Cause: An attempt was made to drop a system-generated sequence.
*Action: A system-generated sequence, such as one created for an
identity column, cannot be dropped.

It is still not possible. The only way to completely clean the objects is either to purge the recycle bin to really get rid of the table :

SQL> purge recyclebin ;
Recyclebin purged.

SQL> select * from recyclebin ;
no rows selected

Or simply use the PURGE keyword when dropping the table :

SQL> drop table metal_bands purge ;

Table METAL_BANDS dropped.

SQL> select * from recyclebin ;
no rows selected

Now the sequence is gone for good :

SQL> select * from user_tab_identity_cols ; 
no rows selected

SQL> select * from user_sequences ;
no rows selected

Enhancement request 14779581 on My Oracle Support has been opened to modify this behaviour, but the page is unfortunately not public.