[20130803]12C在表中使用sequence.txt
记得以前学习数据库的时候，第1个接触的数据库是informix，里面可以在表上定义顺序号，这样在插入时无需指定，
保证唯一。（也许记忆有错，毕竟很久没使用它了）。oracle 12c开始支持这种特性，我的感觉这些主要为了别的
数据库移植到oracle上来。
做一个测试看看:
SQL> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t
(
id number GENERATED ALWAYS as identity ( start with 1 increment by 1),
name varchar2(10)
);
开始测试：
SQL> insert into t values(1,'a');
insert into t values(1,'a')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> insert into t (name) values('a');
1 row created.
SQL> commit ;
Commit complete.
--无法指定ID的值。
SQL> select * from t;
ID NAME
---------- --------------------
1 a
SQL> update t set id=2 where id=1;
update t set id=2 where id=1
*
ERROR at line 1:
ORA-32796: cannot update a generated always identity column
--也不能修改。
SQL> insert into t (name) values('b');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from t;
ID NAME
---------- --------------------
1 a
2 b
SQL> SELECT DBMS_METADATA.get_ddl ('TABLE', 'T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T')
----------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL E
NABLE,
"NAME" VARCHAR2(10)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
--相当于内嵌了一个seq的定义。
SQL> column sequence_name format a20
SQL> select * from USER_SEQUENCES;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
-------------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
ISEQ$$_92784 1 1.0000E+28 1 N N 20 21 N N
--我当前用户并没有定义SEQUENCE，可以确定这个就是表T里面的。猜测一下92784应该对应的object_id.
SQL> select object_id,data_object_id from dba_objects where wner=user and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
92784 92784
--正好对上。
SQL> select data_default from dba_tab_cols where wner=user and table_name='T' and column_name='ID';
DATA_DEFAULT
------------------------------
"SCOTT"."ISEQ$$_92784".nextval
--这样就很清楚了，ID的缺省值就是等于"SCOTT"."ISEQ$$_92784".nextval，通过这个方式获取下一个值。
--继续测试
SQL> select "ISEQ$$_92784".nextval from dual ;
NEXTVAL
----------
3
SQL> insert into t (name) values('c');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from t ;
ID NAME
---------- --------------------
1 a
2 b
4 c
--可以发现跳过了3.另外这些信息的获得使用10046跟踪就可以了。
--补充1点，这样的话建立seq1号，然后定义缺省值为seq1.nextval应该也可以，测试看看。
SQL> CREATE SEQUENCE SCOTT.seq1 START WITH 10 INCREMENT BY 1 MINVALUE 10 CACHE 100 NOCYCLE ORDER ;
Sequence created.
SQL> CREATE TABLE T1
(
ID NUMBER DEFAULT seq1.nextval NOT NULL,
NAME VARCHAR2(10 BYTE)
);
SQL> insert into t1 (name) values ('aaa');
1 row created.
SQL> select * from t1;
ID NAME
---------- --------------------
10 aaa
--如果sequence改名或者删除，插入DML就无法执行。