Sequence练习

~~~~~~~~~~~~~~~~~~
准备工作
~~~~~~~~~~~~~~~~~~
CREATE SEQUENCE SEQ_NOCACHE
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 9999
  NOCACHE;
CREATE SEQUENCE SEQ_CACHE
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 9999
  CACHE 100;
drop table t; 
create table t (x varchar2(5),y varchar2(5));
~~~~~~~~~~~~~~~~~~
正常操作
~~~~~~~~~~~~~~~~~~
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
commit;
select * from t;
                SQL> select * from t;
                X     Y
                ----- -----
                1     1
                2     2
                3     3
                4     4
                5     5
                --两列显示有序
~~~~~~~~~~~~~~~~~~
内存被刷新
~~~~~~~~~~~~~~~~~~
alter system flush shared_pool;
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
select * from t;
                SQL> select * from t;
                X     Y
                ----- -----
                1     1
                2     2
                3     3
                4     4
                5     5
                6     101
                7     102
                8     103
                ----x有序，y无序，因为内存被刷新，cache中的sequence被清空了。

~~~~~~~~~~~~~~~~~~
pin住
~~~~~~~~~~~~~~~~~~
commit;
exec DBMS_SHARED_POOL.KEEP('TESTUSER.SEQ_CACHE','Q');
alter system flush shared_pool;
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
                SQL> select * from t;
                X     Y
                ----- -----
                1     1
                2     2
                3     3
                4     4
                5     5
                6     101
                7     102
                8     103
                9     104
                10    105
                ----x有序，y从103以后也有序，因为sequence被pin住了，即使内存被刷新也不会被清掉。
~~~~~~~~~~~~~~~~~~
shutdown immediate
~~~~~~~~~~~~~~~~~~
commit;
shutdown immediate
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
                SQL> select * from t;
                X     Y
                ----- -----
                1     1
                2     2
                3     3
                4     4
                5     5
                6     101
                7     102
                8     103
                9     104
                10    105
                11    106
                12    107
                13    108
                14    109
                15    110
                ----x有序，y从103以后也有序，因为sequence被pin住了，即使内存被刷新也不会被清掉。

~~~~~~~~~~~~~~~~~~
shutdown abort
~~~~~~~~~~~~~~~~~~
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
insert into t values (SEQ_NOCACHE.nextval,SEQ_CACHE.nextval);
                SQL> select * from t;

                X     Y
                ----- -----
                1     1
                2     2
                3     3
                4     4
                5     5
                6     101
                7     102
                8     103
                9     104
                10    105
                11    106
                X     Y
                ----- -----
                12    107
                13    108
                14    109
        15    110
                19    211
                20    212
                21    213
                ----即使被pin住，在掉电了情况下也会丢失sequence;