PLS-00201: ORA-20000:

PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared

1 what is DBMS_SHARED_POOL

Mos 解釋

The DBMSSHAREDPOOL package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored. DBMSSHAREDPOOL enables you to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.

DBMS_SHARED_POOL包提供存儲(chǔ)過(guò)程來(lái)將PL/SQL對(duì)象或SQL游標(biāo)固定到Oracle 共享池。一旦這些對(duì)象固定之后，將不再參與paged out,而是常駐內(nèi)存，即便是使用alter system flush shared_pool也不會(huì)將對(duì)象清除出共享池（當(dāng)然重啟數(shù)據(jù)庫(kù)相關(guān)對(duì)象還是會(huì)被清除掉），這樣的好處是可以避免大值對(duì)象裝載進(jìn)共享池導(dǎo)致ORA-04031錯(cuò)誤或系統(tǒng)性能問(wèn)題

2 安裝 DBMS_SHARED_POOL

說(shuō)明：DBMS_SHARED_POOL缺省不會(huì)隨系統(tǒng)安裝 ，必須先安裝才可以使用。對(duì)應(yīng)的sql腳本為：dbmspool.sql

SQL> show user;
USER is "SYS"
SQL> @?/rdbms/admin/dbmspool.sql

3 非sys用戶如何使用包： DBMS_SHARED_POOL

用戶使用該包首先需要sys授予執(zhí)行權(quán)限：
grant EXECUTE DBMS_SHARED_POOL to <USERNAME>;

4 DBMS_SHARED_POOL 包常用的存儲(chǔ)過(guò)程

1）DBMSSHAREDPOOL.KEEP 存儲(chǔ)過(guò)程

作用：用于將對(duì)象固定到共享池

參數(shù)解釋：

PROCEDURE DBMS_SHARED_POOL.KEEP (name IN VARCHAR2 ,flag IN CHAR DEFAULT 'P');

Flag標(biāo)志                      Description
----------                   --------------     
C                              cursor                              
JC                             java class                          
JD                             java shared data                    
JR                             java resource                       
JS                             java source                         
P                              Package, procedure, or function name
Q                              sequence                            
R                              trigger                             
T                              type                                 
Any other character             Cursor specified by address and hash value

示例：

 exec sys.dbms_shared_pool.keep('TRADE.TRG_KEEP_SEQ');
 exec sys.dbms_shared_pool.keep('scott.city','T')

2).DBMSSHAREDPOOL.UNKEEP 存儲(chǔ)過(guò)程

作用：用于將對(duì)象從清出保留池

參數(shù)解釋：參考1）

示例：

exec sys.dbms_shared_pool.unkeep('TRADE.TRG_KEEP_SEQ','P')

3）DBMSSHAREDPOOL.SIZES 存儲(chǔ)過(guò)程

作用：該過(guò)程顯示在共享池中超過(guò)指定值大小的對(duì)象，包括游標(biāo)以及匿名的PL/SQL塊。(指定值的大小的單位為kbytes)

參數(shù)解釋：

PROCEDURE DBMS_SHARED_POOL.SIZES (minsize IN NUMBER);

示例： execute sys.dbmssharedpool.sizes(70);

4）ABORTED_REQUEST_THRESHOLD

作用：設(shè)定一個(gè)闕值尺寸，當(dāng)該闕值被設(shè)定后，一個(gè)大于該設(shè)定值的對(duì)象被裝載到共享池時(shí)。避免在共享池沒(méi)有足夠的空間時(shí)，未固定內(nèi)存對(duì)象被清理的情況。可能產(chǎn)生的問(wèn)題：ORA-4031

取值范圍：該值在5000 - 2147483647之間，

示例：

PROCEDURE DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size IN NUMBER); 

execute SYS.DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD(50000);

5 與此相關(guān)的一起sql語(yǔ)句

1）查找需要常駐共享池的對(duì)象

SELECT *
FROM v$db_object_cache
WHERE sharable_mem > 10000      /*此參數(shù)為占住內(nèi)存的大小，可自行設(shè)定大小*/
AND (TYPE='PACKAGE' OR TYPE='PACKAGE BODY' OR TYPE='FUNCTION' OR TYPE='PROCEDURE')
AND kept='NO';

2）sql語(yǔ)句常駐內(nèi)存

SQL> select count(*) from all_objects;

    COUNT(1)
    --------
    40793

SQL> select address,hash_value,sql_text from v$sqlarea where sql_text='select count(*) from all_objects';

ADDRESS       HASH_VALUE SQL_TEXT
-------- --------------- ----------------------------------------
2D33FF58      789896629 select count(*) from all_objects 

SQL> exec sys.dbms_shared_pool.keep('2D33FF58,789896629','C');

PL/SQL procedure successfully completed.

如果我們要取消固定到內(nèi)存的話，則調(diào)用DBMS_SHARED_POOL.UNKEEP即可，該過(guò)程的參數(shù)與KEEP相同。

3）清空share pool的命令(如果在使用包keep對(duì)象沒(méi)有可用空間時(shí)，可以flush shared_pool)

ALTER SYSTEM FLUSH SHARED_POOL;    --此操作不會(huì)清除常駐內(nèi)存的對(duì)象

4)查看當(dāng)前已經(jīng)常駐內(nèi)存的對(duì)象

select * from v$db_object_cache where kept='YES';

5)尋找較大匿名的PL/SQL 塊將其分割為小的PL/SQL塊，以提高共享池的利用率

SELECT sql_text
FROM v$sqlarea
WHERE command_type=47
AND LENGTH(sql_text)>500;

7.解決開頭問(wèn)題

SQL> show user;
USER is "SYS"
SQL> @?/rdbms/admin/dbmspool.sql
SQL> exec sys.dbms_shared_pool.sizes(0)

8.解決問(wèn)題時(shí)碰到其它的錯(cuò)誤

報(bào)錯(cuò)：

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

解決方法：

PL/SQL: DBMS_OUTPUT.ENABLE (buffer_size => NULL);
SQL*Plus: set serveroutput on size unlimited