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 ;

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