Skip to Main Content
  • Questions
  • user_tab_identity_cols.generation_type and BY DEFAULT ON NULL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Matthias.

Asked: April 13, 2026 - 12:53 pm UTC

Last updated: April 14, 2026 - 12:35 pm UTC

Version: 19

You Asked

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

SQL> create table t1(id int generated BY DEFAULT ON NULL as identity);

Tabelle wurde erstellt.

SQL> create table t2(id int generated BY DEFAULT as identity);

Tabelle wurde erstellt.

SQL> create table t3(id int generated ALWAYS as identity);

Tabelle wurde erstellt.

SQL> select table_name, generation_type
  2  from user_tab_identity_cols utic
  3  where utic.table_name in ('T1', 'T2', 'T3');

TABLE_NAME
--------------------------------------------------------------------------------
GENERATION
----------
T1
BY DEFAULT

T2
BY DEFAULT

T3
ALWAYS


Why doesn't user_tab_identity_cols.generation_type show "BY DEFAULT ON NULL" for T1 ?

Behaviour is differently in comparison to T2, so where can I see it (besides DBMS_METADATA) ?

SQL> set long 5000 lines 300 pages 5000
SQL> select dbms_metadata.get_ddl('TABLE', table_name) from user_tables where table_name in ('T1', 'T2');

DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME)
--------------------------------------------------------------------------------

  CREATE TABLE "YYY"."T1"
   (    "ID" NUMBER(*,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVAL
UE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NO
CYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "XXX"


  CREATE TABLE "YYY"."T2"
   (    "ID" NUMBER(*,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 99999
99999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  N
OKEEP  NOSCALE  NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "XXX"


and Chris said...

Good spot - I think this just got overlooked. The ON NULL [ FOR INSERT { ONLY | AND UPDATE } ] option in 26ai is also missing from this view.

I've created an ER (Enh 39207226 - INCLUDE DEFAULT ON NULL OPTIONS FOR IDENTITY COLUMNS IN *_TAB_IDENTITY_COLS VIEWS) for this.

More to Explore

DBMS_METADATA

More on PL/SQL routine DBMS_METADATA here