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包提供存儲過程來將PL/SQL對象或SQL游標固定到Oracle 共享池。一旦這些對象固定之后，將不再參與paged out,而是常駐內存，即便是使用alter system flush shared_pool也不會將對象清除出共享池（當然重啟數據庫相關對象還是會被清除掉），這樣的好處是可以避免大值對象裝載進共享池導致ORA-04031錯誤或系統性能問題

2 安裝 DBMS_SHARED_POOL

說明：DBMS_SHARED_POOL缺省不會隨系統安裝 ，必須先安裝才可以使用。對應的sql腳本為：dbmspool.sql

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

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

用戶使用該包首先需要sys授予執行權限：
grant EXECUTE DBMS_SHARED_POOL to <USERNAME>;

4 DBMS_SHARED_POOL 包常用的存儲過程

1）DBMSSHAREDPOOL.KEEP 存儲過程

作用：用于將對象固定到共享池

參數解釋：

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

Flag標志                      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 存儲過程

作用：用于將對象從清出保留池

參數解釋：參考1）

示例：

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

3）DBMSSHAREDPOOL.SIZES 存儲過程

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

參數解釋：

PROCEDURE DBMS_SHARED_POOL.SIZES (minsize IN NUMBER);

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

4）ABORTED_REQUEST_THRESHOLD

作用：設定一個闕值尺寸，當該闕值被設定后，一個大于該設定值的對象被裝載到共享池時。避免在共享池沒有足夠的空間時，未固定內存對象被清理的情況。可能產生的問題：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 與此相關的一起sql語句

1）查找需要常駐共享池的對象

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

2）sql語句常駐內存

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.

如果我們要取消固定到內存的話，則調用DBMS_SHARED_POOL.UNKEEP即可，該過程的參數與KEEP相同。

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

ALTER SYSTEM FLUSH SHARED_POOL;    --此操作不會清除常駐內存的對象

4)查看當前已經常駐內存的對象

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.解決開頭問題

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

8.解決問題時碰到其它的錯誤

報錯：

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