一、?????問題及現象描述

--創建序列

CREATE SEQUENCE NYYTEST.S_TEST

????START WITH 1

????INCREMENT BY 1

????NOMINVALUE

????MAXVALUE 10000

????NOCYCLE

????CACHE??20

????NOORDER

SQL> select S_TEST.nextval from dual;

???NEXTVAL

----------

?????????1

SQL> select S_TEST.nextval from dual;

???NEXTVAL

----------

?????????2

SQL> select S_TEST.nextval from dual;

???NEXTVAL

----------

?????????3

SQL> select S_TEST.nextval from dual;

???NEXTVAL

----------

?????????4

SQL> select S_TEST.nextval from dual;

???NEXTVAL

----------

?????????5

--刷新共享池，刷新共享池會使所有的沒有使用DBMS_SHARED_POOL.KEEP固定的對象全部被清除，所以共享池剛剛刷新的時候，SQL和PL/SQL的執行效率會略微下降

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系統已更改。

--由于cache的20個序列號已經從共享池中被清除，下次再取序列的nextval值取的是21

SQL> select S_TEST.nextval from dual;

???NEXTVAL

----------

????????21

關于序列設置cache之后跳號問題的原因：cache會把sequence緩存在lb?cache中，在lb?cache中對對象的age?out是基于lru算法的，如果cache?20，會把這個序列每次取的時候取出來20個，然后再在lb?cache中一個一個用，但是如果在用完這20個之前，這個序列被aged?out了，那么沒用的那些數就丟掉了，而下次再去從dd取出sequence的時候就會去取上次那20個+1的號為開始，再來20個。這就是產生斷號的原因。

二、?????dbms_shared_pool包

為了避免上述情況，ORACLE給我們提供了把SEQUENCE KEEP到SHARED POOL中的方法，這樣就保證了SEQUENCE的CACHE不會被交換出去，從而避免了這種情況的發生。

如果將對象固定在內存中，那么在下一次關閉數據庫之前，這個對象就不會失效或者被清空。還需要考慮的是，Metalink的注意事項61760.1:DBMS_SHARED_POOL將被創建為用戶SYS。其他用戶不擁有這個包。需要訪問這個包的任何用戶都必須由SYS授予執行權限。如果在SYS模式中創建這個包并在不同的模式中運行示例代碼，則首先必須給用戶賦予DBMS_SHARED_POOL上的EXECUTE權限。

默認情況下dbms_shared_pool包是不在系統中的，需要運行$ORACLE_HOME/rdbms/admin/dbmspool.sql進行創建

?

SQL> desc dbms_shared_pool;

ERROR:

ORA-04043: 對象 dbms_shared_pool 不存在

?

--創建

SQL> @E:\oracle\RDBMS\ADMIN\dbmspool.sql

程序包已創建。

授權成功。

視圖已創建。

程序包體已創建。

?

--查看包對象

SQL> desc dbms_shared_pool;

PROCEDURE ABORTED_REQUEST_THRESHOLD

參數名稱???????????????????????類型????????????????????輸入/輸出默認值?

------------------------------ ----------------------- ------ --------

?THRESHOLD_SIZE?????????????????NUMBER??????????????????IN

PROCEDURE KEEP

參數名稱???????????????????????類型????????????????????輸入/輸出默認值?

------------------------------ ----------------------- ------ --------

?NAME???????????????????????????VARCHAR2????????????????IN

?FLAG???????????????????????????CHAR????????????????????IN?????DEFAULT

PROCEDURE SIZES

參數名稱???????????????????????類型????????????????????輸入/輸出默認值?

------------------------------ ----------------------- ------ --------

?MINSIZE????????????????????????NUMBER??????????????????IN

PROCEDURE UNKEEP

參數名稱???????????????????????類型????????????????????輸入/輸出默認值?

------------------------------ ----------------------- ------ --------

?NAME???????????????????????????VARCHAR2????????????????IN

?FLAG???????????????????????????CHAR????????????????????IN?????DEFAULT

?

--授權

SQL> grant execute on dbms_shared_pool to nyytest;

授權成功。

SQL> create synonym nyytest.dbms_shared_pool for dbms_shared_pool;

同義詞已創建。

?

procedure keep參數的解釋：

exec?dbms_shared_pool.keep ?keep共有兩個參數，flag代表前一個參數的類型，
如果不輸入，則默認為package/procedure/function中的一個；

procedure keep(name varchar2, flag char DEFAULT 'P')

name

??--??????The name of the object to keep.??There are two kinds of objects:

??--??????PL/SQL objects, triggers, sequences, types and Java objects,

??--??????which are specified by name, and

??--??????SQL cursor objects which are specified by a two-part number

??--??????(indicating a location in the shared pool).??For example:

??--????????dbms_shared_pool.keep('scott.hispackage')

??--??????will keep package HISPACKAGE, owned by SCOTT.??The names for

??--??????PL/SQL objects follows SQL rules for naming objects (i.e.,

??--??????delimited identifiers, multi-byte names, etc. are allowed).

??--??????A cursor can be keeped by

??--????????dbms_shared_pool.keep('0034CDFF, 20348871', 'C')

??--??????The complete hexadecimal address must be in the first 8 characters.

??--??????The value for this identifier is the concatenation of the

??--??????'address' and 'hash_value' columns from the v$sqlarea view.??This

??--??????is displayed by the 'sizes' call above.

??--??????Currently 'TABLE' and 'VIEW' objects may not be keeped.

??flag

??--??????This is an optional parameter.??If the parameter is not specified,

??--????????the package assumes that the first parameter is the name of a

??--????????package/procedure/function and will resolve the name.??Otherwise,

??--????????the parameter is a character string indicating what kind of object

??--????????to keep the name identifies.??The string is case insensitive.

??--????????The possible values and the kinds of objects they indicate are

??--????????given in the following table:

??--????????Value????????Kind of Object to keep

??--????????-----????????----------------------

??--????????P??????????package/procedure/function

??--????????Q??????????sequence

??--????????R??????????trigger

??--????????T??????????type

??--???????JS?????????java source

??--???????JC?????????java class

??--????????JR?????????java resource

??--????????JD?????????java shared data

??--????????C??????????cursor

?

測試結果

S_TEST和S_TEST2序列都是設置cache20，將s_test序列keep到shared pool中

SQL> select S_TEST.nextval from dual;

?

???NEXTVAL

----------

????????24

?

SQL> select S_TEST.nextval from dual;

?

???NEXTVAL

----------

????????25

?

SQL> select S_TEST2.nextval from dual;

?

???NEXTVAL

----------

?????????1

SQL> exec dbms_shared_pool.keep('s_test','q');

?

PL/SQL 過程已成功完成。

?

SQL> select S_TEST2.nextval from dual;

?

???NEXTVAL

----------

????????21

?

SQL> select S_TEST.nextval from dual;

?

???NEXTVAL

----------

????????26

-- dbms_shared_pool.unkeep的作用是取消已經keep在shared pool中的對象

SQL> exec dbms_shared_pool.unkeep('s_test','q');

?

PL/SQL 過程已成功完成。

?

SQL> select S_TEST.nextval from dual;

?

???NEXTVAL

----------

????????27

SQL> alter system flush shared_pool;

?

系統已更改。

?

SQL> select S_TEST.nextval from dual;

?

???NEXTVAL

----------

總結

以上是生活随笔為你收集整理的sequence跳号问题及解决办法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。