安裝DBMS_SHARED_POOL包

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指令碼來建立包體。