Tuesday, June 25, 2013

Oracle 12c New Features - Identity columns

In previous versions of Oracle there was no implicit relationship between a primary key and the sequence that maintained it's value, so developers needed to implement this relationship via insert triggers or application code.  Oracle 12c removes that requirement for custom coding by implementing the relationship in the CREATE TABLE statement using the IDENTITY clause.


SQL> CREATE TABLE TEST_TABLE (ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY, TEXT VARCHAR2(100));

Table created.

When we insert into the table the ID column is automatically populated. 

SQL> insert into test_table (text) values ('aaa');
1 row created.

SQL>commit;

Commit complete.

SQL> select * from test_table;

     ID TEXT
------- ----------
      1 aaa

When we created the table a sequence was also created.

SQL> select object_name, object_type from dba_objects where owner = 'TEST';

OBJECT_NAME     OBJECT_TYPE
--------------- --------------------------------------------
SYS_C009854     INDEX
ISEQ$$_91602    SEQUENCE
TEST_TABLE      TABLE

When we drop the table the sequence is left behind, and cannot be manually deleted.

SQL> drop table test_table;

Table dropped.

SQL> select object_name from dba_objects where owner = 'TEST';

OBJECT_NAME
--------------------------------------------------------------------------------
ISEQ$$_91602

SQL> drop sequence ISEQ$$_91602;
drop sequence ISEQ$$_91602
              *
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence

SQL> ! oerr ora 32794
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.

Until ... the recyclebin is purged.  So the sequence is kept to allow the dropped table to be restored if needed.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> select object_name from dba_objects where owner = 'TEST';

no rows selected