[20130803]12C在表中使用sequence.txt

[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就无法执行。