ORACLE 12C基础

1.CON_ID，0为cdb，1为cdb$root, 2为pdb seed，3以上为pdb
2.自增长列

在12c之前，Oracle只能通过sequence来实现这个功能
sys@newtestCDB> create table test(id number generated always as identity , name varchar2(20));

Table created.

Elapsed: 00:00:00.04
sys@newtestCDB> insert into test(name) values('smith');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith3');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith4');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> select * from test;

    ID NAME

     1 smith     2 smith3     3 smith4

Elapsed: 00:00:00.02
sys@newtestCDB> update test set id=1 where id=2;
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
sys@newtestCDB> insert into test(id,name) values(null,'smith4');
insert into test(id,name) values(null,'smith4')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(id,name) values(2,'smith4');
insert into test(id,name) values(2,'smith4')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
sys@newtestCDB> delete from test where id=3;

1 row deleted.

Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(name) values('smith5');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> select * from test;

    ID NAME

     2 smith3     4 smith5

Elapsed: 00:00:00.01
结论：
GENERATED ALWAYS AS IDENTITY 可以不指定该列进行插入
GENERATED ALWAYS AS IDENTITY不能在该列中插入NULL值
GENERATED ALWAYS AS IDENTITY不能指定具体值插入
GENERATED ALWAYS AS IDENTITY 不能使用update更新该列
sys@newtestCDB> create table test(id number generated by default as identity , name varchar2(20));

Table created.

Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(name) values('smith');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith3');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(name) values('smith4');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> insert into test(id,name) values(null,'smith4');
insert into test(id,name) values(null,'smith4')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."TEST"."ID")

Elapsed: 00:00:00.02
sys@newtestCDB> insert into test(id,name) values(2,'smith3');

1 row created.

Elapsed: 00:00:00.01
sys@newtestCDB> update test set id = NULL where id=2;
update test set id = NULL where id=2
*
ERROR at line 1:
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
sys@newtestCDB> create table test(id number generated by default ON NULL as identity , name varchar2(20));

Table created.

Elapsed: 00:00:00.03
sys@newtestCDB> insert into test(id,name) values(null,'smith4');

1 row created.

Elapsed: 00:00:00.01
结论:

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更新该列
sys@newtestCDB> col tablename format A20
sys@newtestCDB> col table_name format A20
sys@newtestCDB> col sequence_name format A20
sys@newtestCDB> SELECT a.name AS table_name,
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

Elapsed: 00:00:00.01
sys@newtestCDB> create table test(id number generated by default as identity , name varchar2(20));

Table created.

Elapsed: 00:00:00.03
sys@newtestCDB> SELECT a.name AS table_name,
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$$_83964

Elapsed: 00:00:00.01
sys@newtestCDB> SELECT object_name, object_type FROM user_objects where object_name='ISEQ$$_83964';

OBJECT_NAME

OBJECT_TYPE

ISEQ$$_83964
SEQUENCE

Elapsed: 00:00:00.05
sys@newtestCDB> drop table test;

Table dropped.

Elapsed: 00:00:00.04
sys@newtestCDB> SELECT object_name, object_type FROM user_objects where object_name='ISEQ$$_83964';

no rows selected

Elapsed: 00:00:00.00
sys@newtestCDB> create table test(id number generated by default as identity , name varchar2(20));

Table created.

Elapsed: 00:00:00.06
sys@newtestCDB> SELECT a.name AS table_name,
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$$_83966

Elapsed: 00:00:00.01
sys@newtestCDB> drop SEQUENCE ISEQ$$_83966;
drop SEQUENCE ISEQ$$_83966
*
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence

Elapsed: 00:00:00.02
结论：
Identity Columns 是基于序列实现的
GENERATED IDENTITY 中sequence不能单独被删除
GENERATED IDENTITY 中sequence 表被删除时同时删除
这是12.1的图，12.2还有新变化