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
ALWAYSWhy 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"
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.