oracle 12c 列自增长 identity column

-- 针对mssql，mysql的列子增长优势，oracle12c实现了数据库的列自增长功能，其实内部还是用序列来实现的，SQL> alter session set container=pdb1;会话已更改。SQL> alter session set container=cdb$root;ERROR:ORA-01031: 权限不足SQL> select count(1) from user_objects;  COUNT(1)----------         0SQL> create table t1(id number GENERATED AS IDENTITY,name varchar2(10));表已创建。SQL> select object_name,object_type from user_objects;OBJECT_NAME------------------------------------------------------------------------------OBJECT_TYPE----------------------------------------------T1TABLEISEQ$$_92786SEQUENCESQL> set long 500SQL> select dbms_metadata.get_ddl('TABLE','T1') from dual;DBMS_METADATA.GET_DDL('TABLE','T1')------------------------------------------------------------------------------  CREATE TABLE "LOGE"."T1"   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULLNABLE,          "NAME" VARCHAR2(10)   ) SEGMENT CREATION DEFERRED  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  TABLESPACE "USERS"SQL> insert into t1 values('china');insert into t1 values('china')            *第 1 行出现错误:ORA-00947: 没有足够的值SQL> insert into t1(name) values('china');已创建 1 行。SQL> commit;提交完成。SQL> select * from t1;        ID NAME---------- --------------------         1 chinaSQL> insert into t1 values(2,'usa');insert into t1 values(2,'usa')*第 1 行出现错误:ORA-32795: 无法插入到“始终生成”身份列SQL> update t1 set id=100 ;update t1 set id=100              *第 1 行出现错误:ORA-32796: 无法更新“始终生成”身份列SQL> select value from v$diag_info where name='Default Trace File';VALUE------------------------------------------------------------------------------G:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_7744.trcSQL> alter session set events '10046 trace name context forever,level 12';会话已更改。SQL> insert into t1 (name) values('Japan');已创建 1 行。SQL> commit;提交完成。SQL> alter session set events '10046 trace name context forever,level 12';会话已更改。-- 查看PARSING IN CURSOR #523432848 len=37 dep=0 uid=110 oct=2 lid=110 tim=92491773830 hv=1360139914 ad='7ff7ae59e50' sqlid='37wwf9j8j45na'insert into t1 (name) values('Japan')END OF STMTPARSE #523432848:c=0,e=76132,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4015265140,tim=92491773828EXEC #523432848:c=0,e=11550,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=4015265140,tim=92491785524STAT #523432848 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T1 (cr=1 pr=0 pw=0 time=11469 us)'STAT #523432848 id=2 cnt=1 pid=1 pos=1 obj=92787 op='SEQUENCE  ISEQ$$_92786 (cr=0 pr=0 pw=0 time=11142 us)'WAIT #523432848: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=92491785935--修改属性SQL> alter table t1 modify (id number generated as identity minvalue 1 maxvalue999999 increment by 2 start with 1 cache 10);表已更改。-- 另外还有GENERATED BY DEFAULT AS IDENTITY、GENERATED BY DEFAULT ON NULL AS IDENTITY属性-- 2，12c之前实现方式SQL> drop table t2 purge;表已删除。SQL> create table t2 (id int,name varchar2(10));表已创建。SQL> create sequence seq_t2  2  increment by 1 minvalue 1 maxvalue 9999999999 start with 1 cache 10 order;序列已创建。SQL> create trigger t_t2  2  before insert on t2  3  for each row  4  begin  5  select seq_t2.nextval into :new.id from dual;  6  end;  7  /触发器已创建SQL> insert into t2(name) values('china');已创建 1 行。SQL> commit;提交完成。SQL> insert into t2(name) values('china');已创建 1 行。SQL> select * from t2;        ID NAME---------- --------------------         1 china         2 china