ORACLE 12C基础

isgood

　　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>　　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('smith2');
　　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> select * from test;
　　

   >　　

　　

     1 smith　　2 smith2
　　3 smith3
　　

　　Elapsed: 00:00:00.02

　　sys@newtestCDB> update test set>
　　update test set>　　*
　　ERROR at line 1:

　　ORA-32796: cannot update a generated always>　　Elapsed: 00:00:00.03
　　sys@newtestCDB> insert into test(id,name) values(null,'smith3');
　　insert into test(id,name) values(null,'smith3')
　　*
　　ERROR at line 1:

　　ORA-32795: cannot insert into a generated always>　　Elapsed: 00:00:00.01
　　sys@newtestCDB> insert into test(id,name) values(2,'smith3');
　　insert into test(id,name) values(2,'smith3')
　　*
　　ERROR at line 1:

　　ORA-32795: cannot insert into a generated always>
　　sys@newtestCDB> delete from test where>　　1 row deleted.
　　Elapsed: 00:00:00.03
　　sys@newtestCDB> insert into test(name) values('smith4');
　　1 row created.
　　Elapsed: 00:00:00.01
　　sys@newtestCDB> select * from test;
　　

   >　　

　　

     2 smith2　　4 smith4
　　

　　Elapsed: 00:00:00.01
　　结论：

　　GENERATED ALWAYS AS>
　　GENERATED ALWAYS AS>
　　GENERATED ALWAYS AS>
　　GENERATED ALWAYS AS>
　　sys@newtestCDB>  create table test(id number generated by default as>　　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('smith2');
　　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(id,name) values(null,'smith3');
　　insert into test(id,name) values(null,'smith3')
　　*
　　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,'smith2');
　　1 row created.
　　Elapsed: 00:00:00.01

　　sys@newtestCDB> update test set>
　　update test set>　　*
　　ERROR at line 1:
　　ORA-01407: cannot update ("SYS"."TEST"."ID") to NULL
　　结论：

　　GENERATED BY DEFAULT AS>
　　GENERATED BY DEFAULT AS>
　　GENERATED BY DEFAULT AS>
　　GENERATED BY DEFAULT AS>
　　sys@newtestCDB>  create table test(id number generated by default ON NULL as>　　Table created.
　　Elapsed: 00:00:00.03
　　sys@newtestCDB> insert into test(id,name) values(null,'smith3');
　　1 row created.
　　Elapsed: 00:00:00.01
　　结论:

　　GENERATED BY DEFAULT ON NULL AS>
　　GENERATED BY DEFAULT ON NULL AS>
　　GENERATED BY DEFAULT ON NULL AS>
　　GENERATED BY DEFAULT ON NULL AS>　　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>　　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>　　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>
　　GENERATED>

这是12.1的图，12.2还有新变化