Oracle Identity Columns
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Identity Columns in Oracle bring to the Oracle Database a capability that looks provides analogous functionality to Identity Columns in other database products such as Informix, SQL Server, and Sybase. The process by which they work is to generate a sequence object and assign it to generate a column default. Identity Columns offer no functionality superior or enhanced over that available using a sequence object.
 
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

Name   Null?      Type
-----  ---------  -----------
RID    NOT NULL   NUMBER
COL               VARCHAR2(3)


SELECT dbms_metadata.get_ddl('TABLE','T1',USER) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1',USER)
------------------------------------------------------------------------------
CREATE TABLE "UWCLASS"."T1"
( "RID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999
999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP N
OSCALE NOT NULL ENABLE
,
"COL" VARCHAR2(3)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "UWDATA"


SELECT table_name
FROM user_tables
WHERE has_identity = 'YES';

TABLE_NAME
------------------------------
T1
QNAME


SELECT column_name, identity_column
FROM user_tab_cols
WHERE table_name = 'T1';

COLUMN_NAME  IDE
------------ ---
RID          YES
COL          NO


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';

TABLE_NAME  COLUMN_NAME  DEF IDE
----------- ------------ --- ---
T1           RID         NO YES
T1           COL         NO NO


desc user_tab_identity_cols

SELECT * FROM sys.idnseq$;

  OBJ#  INTCOL#  SEQOBJ#  STARTWITH
------ -------- -------- ----------
 77284        1    77285          1


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

SELECT data_default
FROM dba_tab_cols
WHERE table_name = 'T1';

DATA_DEFAULT
-------------------------------
"C##SH"."ISEQ$$_77284".nextval


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;

     RID COL
-------- ---
      1  A
      3  B
      5  C


INSERT INTO t2 (rid, col) VALUES (42, 'C');
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

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
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx