我们在使用sequence时为了提升性能通常都会使用sequence的cache功能,但是潜在的危险就是实例crash或者由于shared pool碎片较多容易出现ora-04031错误时,此时我们都会想到alter system flush shared pool...flush之后cache的sequence值全部丢失从而造成序列不连续...
[@more@]--事先创建一个序列
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
1
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
2
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
3
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
4
--在flush shard poo之后很明显被cache的sequence值全部丢失了
SQL> alter system flush shared_pool;
系统已更改。
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
21
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
22
--为了保证能不丢失被cache的sequence值,那么在flush shared pool之前最好使序列暂时nocache
SQL> alter sequence seq_test nocache;
序列已更改。
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
23
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
24
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
25
SQL>
--如果既想使用序列的cache功能还想使flush shared pool使cached sequence值不丢失,此时可以把这个squence keep在shared pool中
SQL> alter sequence seq_test cache 20;
序列已更改。
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
26
SQL> alter system flush shared_pool;
系统已更改。
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
46
SQL> exec dbms_shared_pool.keep('seq_test','Q')
PL/SQL 过程已成功完成。
SQL> col name format a10
SQL> select name,kept from v$db_object_cache where name='SEQ_TEST';
NAME KEP
---------- ---
SEQ_TEST YES
SQL> alter sequence seq_test cache 30;
序列已更改。
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
47
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
48
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
49
SQL> alter system flush shared_pool;
系统已更改。
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
50
SQL>
--显然keep在shared pool里面之后再flush shared pool被cache 的sequence值就不会丢失了
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1028567/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1028567/