Oracle 12c has introduced the ability to define a column in a database table as a “Identity” Column. This results in the database automatically using a sequence to generate the values that get inserted into this column. The Oracle-Base blog has an article on how to use this functionality and some performance comparisons between this and trigger based methods.
Below is a bit more detail regarding this functionality.
As stated in the Oracle-Base article, oracle auto-creates a sequence and uses it to generate the values. There seems to be a new data dictionary view, USER_TAB_IDENTITY_COLS, which shows you which columns in the table are defined as the Datatype IDENTITY.
The query below can be used to, identify the sequence name, that oracle generated, for a given column, defined as an IDENTITY datatype.
SQL> set long 999999999
SQL> select data_default from user_tab_columns where table_name = 'T1'
2 and column_name = 'COL1';
DATA_DEFAULT
--------------------------------------------------------------------------------
"SCOTT"."ISEQ$$_92434".nextval
SEQUENCE_NAME INCREMENT_BY CACHE_SIZE
-------------------- ------------ ----------
ISEQ$$_92434 1 20
The sequence it uses, gets created with a default of 20 for the cache size. This will likely be unacceptable in Real Application Clusters environments. If you try to modify the cache size of the sequence after the creation you will get a ORA-32793 error “cannot alter a system-generated sequence”.
In such cases it would be wise to use the syntax below during table creation, and specify the CACHE_SIZE and other sequence parameters you would want to change.
SQL> create table t1 (col1 number generated as identity (start with 100 increment by 2 cache 1000), col2 varchar2(50));
Table created.
Once the table is re-created, you can check the cache size again to verify.
SQL> select sequence_name,cache_size from user_sequences;
SEQUENCE_NAME CACHE_SIZE
-------------------- ----------
ISEQ$$_92436 1000
