oracle@ythdc:/home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 27 17:18:20 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
1.执行dbmspool脚本创建dbms_shared_pool包，默认不存在。
SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.
2.执行权限授予test用户。
SQL> grant execute on dbms_shared_pool to test;
Grant succeeded.
SQL> connect test/test
Connected.
3.创建一个测试存储过程。
SQL> create or replace procedure test.t111
as
t date;
begin
select sysdate into t from dual;
dbms_output.put_line(t);
end t111;
/ 2 3 4 5 6 7 8
Procedure created.
4.将t111存储过程pin到内存中。
SQL> exec sys.dbms_shared_pool.keep('t111');
PL/SQL procedure successfully completed.
5.查看是否pin成功。
SQL> COL OWNER FORMAT A10;
SQL> COL NAME FORMAT A30;
SQL> select OWNER,NAME,TYPE,KEPT from V$db_Object_Cache where name='T111';
OWNER NAME TYPE KEP
---------- ------------------------------ ---------------------------- ---
TEST T111 PROCEDURE YES
6.查看sys.dbms_shared_pool定义。
SQL> desc sys.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
FLAG的值如下：
Value Kind of Object to keep
----- ----------------------
P package/procedure/function(默认)
Q sequence
R trigger
T type
JS java source
JC java class
JR java resource
JD java shared data
C cursor
dbms_shared_pool.purge的使用参考文章：http://space.itpub.net/471666/viewspace-616339
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-688279/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-688279/