Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1030924
  • 博文数量: 159
  • 博客积分: 3063
  • 博客等级: 中校
  • 技术积分: 2703
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-01 01:51
文章分类

全部博文(159)

文章存档

2013年(48)

2012年(111)

分类: Oracle

2012-04-07 09:34:12

~~~~~~~~~~~~~~~~~~
准备工作
~~~~~~~~~~~~~~~~~~
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;

阅读(728) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~