oracle数据库共享池如何设置,Oracle数据库如何使用DBMS_SHARED_POOL包将对象固定在共享池...

DBMS_SHARED_POOL可以由?\RDBMS\ADMIN\DBMSPOOL.SQL创建

SQL> @?\rdbms\admin\dbmspool.sql

程序包已创建。

授权成功。

视图已创建。

程序包体已创建。

查询v$db_object_cache视图，看dbms_job包在library cache中的状态。

v$db_object_cache用于查询cache在library cache中的对象信息，这里的对象包括表，

索引，聚簇，同义词，PL/SQL过程，包，触发器。

SQL> col name for a20

SQL> col type for a20

SQL> select name,type,sharable_mem,loads,kept from v$db_object_cache

2   where upper(name)=upper('dbms_job');

NAME                 TYPE                 SHARABLE_MEM      LOADS KEP

-------------------- -------------------- ------------ ---------- ---

DBMS_JOB             NOT LOADED                      0          1 NO

DBMS_JOB             NOT LOADED                      0          1 NO

对dbms_job包进行keep操作

SQL> exec dbms_shared_pool.keep('dbms_job');

PL/SQL 过程已成功完成。

SQL> select name,type,sharable_mem,loads,kept from v$db_object_cache

2   where upper(name)=upper('dbms_job');

NAME                 TYPE                 SHARABLE_MEM      LOADS KEP

-------------------- -------------------- ------------ ---------- ---

DBMS_JOB             PACKAGE                     20828          2 YES

DBMS_JOB             PACKAGE BODY                12636          2 YES

对dbms_job包进行unkeep操作

SQL> exec dbms_shared_pool.unkeep('dbms_job');

PL/SQL 过程已成功完成。

SQL> select name,type,sharable_mem,loads,kept from v$db_object_cache

2   where upper(name)=upper('dbms_job');

NAME                 TYPE                 SHARABLE_MEM      LOADS KEP

-------------------- -------------------- ------------ ---------- ---

DBMS_JOB             PACKAGE                     20828          2 NO

DBMS_JOB             PACKAGE BODY                12636          2 NO

FLUSH shared pool

SQL> alter system flush shared_pool;

系统已更改。

SQL> select name,type,sharable_mem,loads,kept from v$db_object_cache

2   where upper(name)=upper('dbms_job');

NAME                 TYPE                 SHARABLE_MEM      LOADS KEP

-------------------- -------------------- ------------ ---------- ---

DBMS_JOB             NOT LOADED                      0          2 NO

DBMS_JOB             NOT LOADED                      0          2 NO