Oracle カラム値の自動採番

CREATE TABLE EX01
   (    
     SID      NUMBER(18,0)
              --GENERATED  ALWAYS AS IDENTITY
              GENERATED BY DEFAULT AS IDENTITY 
              MINVALUE 1 
              MAXVALUE 999999999999999999
              INCREMENT BY 1 
              START WITH 1 
              CACHE 100
              ORDER 
              NOCYCLE
              NOT NULL
    ,SNAME    VARCHAR2(32 CHAR)
    ,BIRTHDAY DATE
    ,NOTE     VARCHAR2(256 CHAR)
    ,PRIMARY KEY (SID)
   )
;Copy

select * from DBA_TAB_IDENTITY_COLS
;Copy

SQL> select t.COLUMN_NAME,t.SEQUENCE_NAME from DBA_TAB_IDENTITY_COLS t
  2  where t.OWNER='SOONI' and t.TABLE_NAME='EX01'
  3  ;

   COLUMN_NAME    SEQUENCE_NAME
______________ ________________
SID            ISEQ$$_100050

SQL> select s.MIN_VALUE,s.MAX_VALUE,s.INCREMENT_BY,s.LAST_NUMBER,s.CACHE_SIZE,s.ORDER_FLAG,s.CYCLE_FLAG
  2   from dba_sequences s where s.SEQUENCE_NAME='ISEQ$$_100050'
  3  ;

   MIN_VALUE             MAX_VALUE    INCREMENT_BY    LAST_NUMBER    CACHE_SIZE    ORDER_FLAG    CYCLE_FLAG
____________ _____________________ _______________ ______________ _____________ _____________ _____________
           1    999999999999999999               1              1           100 Y             N

SQL>
SQL> alter SEQUENCE ISEQ$$_100050 cache  40
  2  ;

次のコマンドの開始中にエラーが発生しました : 行 1 -
alter SEQUENCE ISEQ$$_100050 cache  40
エラー・レポート -
ORA-32793: システム生成の順序は変更できません
32793.0000 -  "cannot alter a system-generated sequence"
*Cause:    An attempt was made to alter a system-generated sequence.
*Action:   A system-generated sequence, such as one created for an
           identity column, cannot be altered.
SQL> alter table ex01  MODIFY ( SID GENERATED BY DEFAULT AS IDENTITY CACHE 40 )
  2  ;

Table EX01が変更されました。

SQL> select s.MIN_VALUE,s.MAX_VALUE,s.INCREMENT_BY,s.LAST_NUMBER,s.CACHE_SIZE,s.ORDER_FLAG,s.CYCLE_FLAG
  2   from dba_sequences s where s.SEQUENCE_NAME='ISEQ$$_100050';

   MIN_VALUE             MAX_VALUE    INCREMENT_BY    LAST_NUMBER    CACHE_SIZE    ORDER_FLAG    CYCLE_FLAG
____________ _____________________ _______________ ______________ _____________ _____________ _____________
           1    999999999999999999               1              1            40 Y             N

SQL>Copy

select  t.OWNER,t.TABLE_NAME,t.SEQUENCE_NAME,s.CACHE_SIZE,s.LAST_NUMBER
from DBA_TAB_IDENTITY_COLS t inner join DBA_SEQUENCES s
on t.OWNER = s.SEQUENCE_OWNER and t.SEQUENCE_NAME= s.SEQUENCE_NAME
where t.TABLE_NAME ='EX01' and t.OWNER='SOONI'
;Copy

ALTER TABLE SOONI.EX01 MODIFY (SID GENERATED ALWAYS AS IDENTITY (START WITH 1000000000)) 
;Copy

ALTER TABLE SOONI.EX01 MODIFY (SID GENERATED BY DEFAULT AS IDENTITY (START WITH 1000000000)) 
;Copy

SQL> select t.OWNER,t.TABLE_NAME,t.GENERATION_TYPE
  2  from DBA_TAB_IDENTITY_COLS t
  3  where t.OWNER='SOONI' and t.TABLE_NAME='EX01'
  4  ;

   OWNER    TABLE_NAME    GENERATION_TYPE
________ _____________ __________________
SOONI    EX01          ALWAYS

SQL>
SQL> insert into EX01
  2  (sid,SNAME, BIRTHDAY, NOTE)
  3  values(10,'西川','2001/01/08','一番の長身')
  4  ;

次のコマンド行の開始中にエラーが発生しました : 1 -
insert into EX01
(sid,SNAME, BIRTHDAY, NOTE)
values(10,'西川','2001/01/08','一番の長身')
コマンド行 : 2 列 : 2 でのエラー
エラー・レポート -
SQLエラー: ORA-32795: GENERATED ALWAYSで作成されたアイデンティティ列には挿入できません
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.
SQL> alter table ex01 MODIFY (SID GENERATED BY DEFAULT AS IDENTITY)
  2  ;

Table EX01が変更されました。

SQL> insert into EX01
  2  (sid,SNAME, BIRTHDAY, NOTE)
  3  values(10,'西川','2001/01/08','一番の長身')
  4  ;

1行挿入しました。

SQL>Copy

ALTER TABLE SOONI.EX01 add (SID  NUMBER(18,0) GENERATED BY DEFAULT AS IDENTITY (START WITH 1000000000)) 
;Copy

ALTER TABLE SOONI.EX01 modify (SID GENERATED BY DEFAULT AS IDENTITY (CACHE 300)) 
;Copy