1.CON_ID，0为cdb，1为cdb$root, 2为pdb seed，3以上为pdb
2.自增长列
在12c之前，Oracle只能通过sequence来实现这个功能
Table created.
Elapsed: 00:00:00.04
1 row created.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.02
update test set id=1 where id=2
*
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
Elapsed: 00:00:00.03
insert into test(id,name) values(null,'smith3')
ORA-32795: cannot insert into a generated always identity column
insert into test(id,name) values(2,'smith3')
1 row deleted.
结论：
GENERATED ALWAYS AS IDENTITY 可以不指定该列进行插入
GENERATED ALWAYS AS IDENTITY不能在该列中插入NULL值
GENERATED ALWAYS AS IDENTITY不能指定具体值插入
GENERATED ALWAYS AS IDENTITY 不能使用update更新该列
ORA-01400: cannot insert NULL into ("SYS"."TEST"."ID")
update test set id = NULL where id=2
ORA-01407: cannot update ("SYS"."TEST"."ID") to NULL
GENERATED BY DEFAULT AS IDENTITY 可以不指定该列进行插入
GENERATED BY DEFAULT AS IDENTITY不能在该列中插入NULL值
GENERATED BY DEFAULT AS IDENTITY 可以指定具体值插入
GENERATED BY DEFAULT AS IDENTITY 可以使用update更新该列，但不能更新为NULL
结论:
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以不指定该列进行插入
GENERATED BY DEFAULT ON NULL AS IDENTITY 方式可以指定具体值插入
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以在该列中插入null值
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以使用update更新该列
2 b.name AS sequence_name
3 FROM sys.idnseq$ c
4 JOIN obj$ a ON c.obj# = a.obj#
5 JOIN obj$ b ON c.seqobj# = b.obj#
6 where a.name='TEST';
TABLE_NAME SEQUENCE_NAME
TEST ISEQ$$_83962
TEST ISEQ$$_83964
ISEQ$$_83964
SEQUENCE
Elapsed: 00:00:00.05
Table dropped.
no rows selected
Elapsed: 00:00:00.00
Elapsed: 00:00:00.06
TEST ISEQ$$_83966
drop SEQUENCE ISEQ$$_83966
ORA-32794: cannot drop a system-generated sequence
Identity Columns 是基于序列实现的
GENERATED IDENTITY 中sequence不能单独被删除
GENERATED IDENTITY 中sequence 表被删除时同时删除
这是12.1的图，12.2还有新变化
本文转自whshurk 51CTO博客，原文链接：http://blog.51cto.com/shurk/2061398，如需转载请自行联系原作者