Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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';
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');