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