CREATE TABLE GC_PINNING
(OBJ_OWNER VARCHAR2(50), OBJ_NAME VARCHAR2(50),OBJ_TYPE VARCHAR2(50),
OBJ_SIZE NUMBER,PINNING_DATE DATE DEFAULT SYSDATE,obj_LOADS NUMBER);
insert into GC_PINNING
(OBJ_OWNER , OBJ_NAME,OBJ_TYPE,
OBJ_SIZE,PINNING_DATE ,obj_LOADS)
(select
OWNER , NAME,TYPE,
sharable_mem,
sysdate
,LOADS
from v$db_object_cache where
type in (‘PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’SEQUENCE’,’PROCEDURE’)
and nvl(sharable_mem,0)>0 and kept=’NO’);
CREATE OR REPLACE PROCEDURE GC_PINNER AS
/*
gc_Pinner by Helio Dias.com
Subject : pinning object that had been saved on GC_PINNING table
June 24, 2008,
*/
cursor c_pinning is
select OBJ_OWNER||’.’||OBJ_NAME OBJ_NAME,OBJ_TYPE from GC_PINNING;
V_NAME VARCHAR2(50);
V_TYPE VARCHAR(50);
begin
open c_pinning;
loop
fetch c_pinning into v_name,v_type;
exit when c_pinning%notfound;
IF V_TYPE=’FUNCTION’
THEN V_TYPE:=’P’;
END IF;
IF V_TYPE=’SEQUENCE’
THEN V_TYPE:=’Q’;
END IF;
IF V_TYPE=’TRIGGER’
THEN V_TYPE:=’R’;
END IF;
V_TYPE:=SUBSTR(V_TYPE,1,1);
dbms_shared_pool.keep(V_NAME,V_TYPE);
end loop;
END;
/
Oracle Logbook 