Learning is not a spectator sport

One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support for the declarative syntax makes migration from other database platforms a lot easier.

Under the covers, identity columns are implemented as sequences. This makes a lot of sense – why invent a new piece of functionality when you can exploit something that already has been tried and tested exhaustively for 20 years? So when you create a table with an identity column, you’ll see the appearance of a system named sequence to support it.

Another nice thing about identity columns is that if you drop the table, the underlying sequence that supports the column is also automatically dropped….. (cue ominous music…) or is it? I had this demo come my way via email asking that exact question:

SQL> create table t1 ( x int generated always as identity );

Table created.

SQL> create table t2 ( x int generated by default as identity );

Table created.

SQL> select table_name, sequence_name
  2  from   user_tab_identity_cols;

TABLE_NAME           SEQUENCE_NAME
-------------------- --------------------
T1                   ISEQ$$_139912
T2                   ISEQ$$_139914

6 rows selected.

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> select table_name, sequence_name
  2  from   user_tab_identity_cols;

no rows selected

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
--------------------
ISEQ$$_139912
ISEQ$$_139914

Is that a bug? Why are the sequences still there?

There is no need to panic – there is a very simple explanation to this. From 10g onwards, by default, when you drop a table we do not actually drop it (ie, release the data and the space it consumed). We simply rename it, and it becomes a member of the recycle bin. This is a nifty insurance policy against losing data inadvertently. The table “disappears” from standard dictionary views but the sequences remain which is what causes the confusion.  We don’t destroy the sequences because recreating them if the table is brought back from the recycle bin would require knowing what the current high watermark for the sequence value would need to be.

Purging the recycle bin brings everything back into alignment.

SQL> purge recyclebin;

Recyclebin purged.

SQL> select sequence_name from user_sequences;

no rows selected