IDENTITY column in Oracle

Prior to Oracle 12c , there was no direct equivalent to Sybase / SQL Server / PostgreSQL  identity column . This enhancement is available in 12c. 

Under the hood , Oracle creates sequence and associate it to the base table.  The  sequence name is tagged as "ISEQ$$_XXXXX"  , where XXXX is the object id of the base table.   The record is available in xxx_sequences ( all / user / pdb) views , till the recycle bin is purged . 

To create a table with the identity column , the user needs to "CREATE TABLE" and "CREATE SEQUENCE" privilege . 

Datapump has been enhanced to accommodate this enhancement as well. Here is an example. 




SQL> show user
USER is "ZAHIR"
SQL>

SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;
Create table t_identity
*
ERROR at line 1:
ORA-01031: insufficient privileges

C:\Users\mohideen>sqlplus system@pdb_test

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 20 13:04:13 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Apr 20 2016 12:42:47 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant create sequence to zahir ;

Grant succeeded.

SQL> conn zahir/password@pdb_test
Connected.
SQL>
SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;

Table created.

SQL> col object_name format a20
SQL> Select  object_name , object_id , object_type  from user_objects where object_name like 'T_IDENT%';

OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -----------------------
T_IDENTITY                92733 TABLE

SQL> col sequence_name format a20
SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> drop table t_identity;

Table dropped.

SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> purge recyclebin ;

Recyclebin purged.

SQL> Select  sequence_name , min_value  from user_sequences ;

no rows selected