Oracle提供了一个管理共享池对象的接口——DBMS_SHARED_POOL包，不过这个包在11g以前的版本是默认没有安装的。
先看一下11.2的情况：
SQL> SELECT * FROM v$version; BANNER -------------------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS FOR Solaris: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 – Production SQL> DESC dbms_shared_pool PROCEDURE ABORTED_REQUEST_THRESHOLD Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- THRESHOLD_SIZE NUMBER IN PROCEDURE KEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT PROCEDURE KEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- SCHEMA VARCHAR2 IN OBJNAME VARCHAR2 IN NAMESPACE NUMBER IN HEAPS NUMBER IN PROCEDURE KEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- HASH VARCHAR2 IN NAMESPACE NUMBER IN HEAPS NUMBER IN PROCEDURE MARKHOT Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- SCHEMA VARCHAR2 IN OBJNAME VARCHAR2 IN NAMESPACE NUMBER IN DEFAULT GLOBAL BOOLEAN IN DEFAULT PROCEDURE MARKHOT Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- HASH VARCHAR2 IN NAMESPACE NUMBER IN DEFAULT GLOBAL BOOLEAN IN DEFAULT PROCEDURE PURGE Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT HEAPS NUMBER IN DEFAULT PROCEDURE PURGE Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- SCHEMA VARCHAR2 IN OBJNAME VARCHAR2 IN NAMESPACE NUMBER IN HEAPS NUMBER IN PROCEDURE PURGE Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- HASH VARCHAR2 IN NAMESPACE NUMBER IN HEAPS NUMBER IN PROCEDURE SIZES Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- MINSIZE NUMBER IN PROCEDURE UNKEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT PROCEDURE UNKEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- SCHEMA VARCHAR2 IN OBJNAME VARCHAR2 IN NAMESPACE NUMBER IN PROCEDURE UNKEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- HASH VARCHAR2 IN NAMESPACE NUMBER IN PROCEDURE UNMARKHOT Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- SCHEMA VARCHAR2 IN OBJNAME VARCHAR2 IN NAMESPACE NUMBER IN DEFAULT GLOBAL BOOLEAN IN DEFAULT PROCEDURE UNMARKHOT Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- HASH VARCHAR2 IN NAMESPACE NUMBER IN DEFAULT GLOBAL BOOLEAN IN DEFAULT SQL> |
11.2中，这个包在数据库创建的时刻就会默认安装完成，而且在11.2中，这个包的功能得到了进一步的增强。除了给一些已有的过程增加了重载的过程外，还新增了MARKHOT以及和它对应的UNMARKHOT过程。MARKHOT用来标记一个LIBRARY CACHE对象为热对象，而UNMARKHOT则取消这个标记。
在10g及以前版本，这个包在数据库创建后并未马上创建需要手工调用$ORACLE_HOME/rdbms/admin/dbmspool.sql来创建：
SQL> SELECT * FROM v$version; BANNER ---------------------------------------------------------------- Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS FOR Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> DESC dbms_shared_pool ERROR: ORA-04043: object dbms_shared_pool does NOT exist SQL> @?/rdbms/admin/dbmspool.sql Package created. GRANT succeeded. VIEW created. Package body created. SQL> DESC dbms_shared_pool PROCEDURE ABORTED_REQUEST_THRESHOLD Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- THRESHOLD_SIZE NUMBER IN PROCEDURE KEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT PROCEDURE PURGE Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT HEAPS NUMBER IN DEFAULT PROCEDURE SIZES Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- MINSIZE NUMBER IN PROCEDURE UNKEEP Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- NAME VARCHAR2 IN FLAG CHAR IN DEFAULT |
在10g中，Oracle将包体创建的调用脚本添加到了dbmspool.sql中，而在更早的版本中，除了需要执行dbmspool.sql脚本意外，还需要手工方式执行$ORACLE_HOME/rdbms/admin/prvtpool.plb脚本来创建包体。