Oracle Identity Columns
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
Data Dictionary Objects
ALL_TAB_IDENTITY_COLS IDNSEQ$ TAB$
CDB_TAB_IDENTITY_COLS SEQ$ USER_TAB_IDENTITY_COLS
DBA_TAB_IDENTITY_COLS    
Exceptions
Error Code Reason
ORA-32795 cannot insert into a generated always identity column
System Privileges
ALTER ANY TABLE CREAT TABLE DROP ANY TABLE
CREATE ANY TABLE IDNSEQ$  
   
 
GENERATED ALWAYS Demo
Generated Always CREATE TABLE <table_name> (
<column_name>  <column_data_type> GENERATED ALWAYS AS IDENTITY [
 INCREMENT BY <integer>
 START WITH <integer>
 MAXVALUE <integer> / NOMAXVALUE
 MINVALUE <integer> / NOMINVALUE
 CYCLE / NOCYCLE
 CACHE <#> / NOCACHE
 ORDER / NOORDER]),
<column_name>  <column_data_type>);
CREATE TABLE t1 (
rid NUMBER GENERATED ALWAYS AS IDENTITY,
col VARCHAR2(3));

desc t1

INSERT INTO t1 (col) VALUES ('A');
INSERT INTO t1 (col) VALUES ('B');
INSERT INTO t1 (col) VALUES ('C');

SELECT * FROM t1;

desc dba_tab_cols
desc dba_tab_columns

SELECT table_name, column_name, default_on_null, identity_column
FROM dba_tab_cols
WHERE table_name = 'T1';

desc user_tab_identity_cols

SELECT * FROM sys.idnseq$;

SELECT * FROM sys.seq$
WHERE obj# IN (
  SELECT seqobj#
  FROM sys.idnseq$);

SELECT data_default
FROM dba_tab_cols
WHERE table_name = 'T1';

CREATE TABLE t2 (
rid NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 2,
col VARCHAR2(3));

INSERT INTO t2 (col) VALUES ('A');
INSERT INTO t2 (col) VALUES ('B');
INSERT INTO t2 (col) VALUES ('C');

SELECT * FROM t2;

INSERT INTO t2 (rid, col) VALUES (42, 'C');

DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t3 (
rid NUMBER GENERATED ALWAYS AS IDENTITY
 INCREMENT BY 2
 START WITH 100
 MAXVALUE 110
 MINVALUE 100
 CYCLE
 CACHE 5
 NOORDER
,
col VARCHAR2(3));

SELECT * FROM sys.seq$
WHERE obj# IN (
  SELECT seqobj#
  FROM sys.idnseq$);

INSERT INTO t3 (col) VALUES ('A');
INSERT INTO t3 (col) VALUES ('B');
INSERT INTO t3 (col) VALUES ('C');
INSERT INTO t3 (col) VALUES ('D');
INSERT INTO t3 (col) VALUES ('E');
INSERT INTO t3 (col) VALUES ('F');

SELECT * FROM t3;

INSERT INTO t3 (col) VALUES ('G');

SELECT * FROM t3;

DROP TABLE t3 PURGE;
 
GENERATED BY DEFAULT Demo
Generated By Default CREATE TABLE <table_name> (
<column_name>  <column_data_type> GENERATED BY DEFAULT AS IDENTITY [
 INCREMENT BY <integer>
 START WITH <integer>
 MAXVALUE <integer> / NOMAXVALUE
 MINVALUE <integer> / NOMINVALUE
 CYCLE / NOCYCLE
 CACHE <#> / NOCACHE
 ORDER / NOORDER]),
<column_name>  <column_data_type>);
CREATE TABLE t4 (
rid NUMBER GENERATED BY DEFAULT AS IDENTITY,
col VARCHAR2(3));

INSERT INTO t4 (col) VALUES ('A');
INSERT INTO t4 (col) VALUES ('B');
INSERT INTO t4 (col) VALUES ('C');

SELECT * FROM t4;

SELECT table_name, column_name, default_on_null, identity_column
FROM dba_tab_cols
WHERE table_name = 'T4';

SELECT * FROM sys.idnseq$;

SELECT * FROM sys.seq$
WHERE obj# IN (
  SELECT seqobj#
  FROM sys.idnseq$);

SELECT data_default
FROM dba_tab_cols
WHERE table_name = 'T4';

CREATE TABLE t5 (
rid NUMBER GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 10,
col VARCHAR2(3));

INSERT INTO t5 (col) VALUES ('A');
INSERT INTO t5 (col) VALUES ('B');
INSERT INTO t5 (col) VALUES ('C');

SELECT * FROM t5;

INSERT INTO t5 (rid, col) VALUES (42, 'C');

SELECT * FROM t5;

Related Topics
Data Dictionary
Sequences
Tables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved