Automating Oracle Oracle Tips by Mike Ault

                     revealnet_kept_objects;
BEGIN
 OPEN get_objects;
 FETCH get_objects INTO object(i);
 LOOP
       EXIT WHEN get_objects%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('UNKEEPING: '||object(i));
        DBMS_SHARED_POOL.UNKEEP(object(i));
       i:=i+1;
       FETCH get_objects INTO object(i);
 END LOOP;
 CLOSE get_objects;
 com_cur:=DBMS_SQL.OPEN_CURSOR;
 com:='ALTER SYSTEM FLUSH SHARED_POOL';
 DBMS_SQL.PARSE(com_cur,com,dbms_sql.v7);
 processed:=DBMS_SQL.EXECUTE(com_cur);
 COMMIT;
 processed:=DBMS_SQL.EXECUTE(com_cur);
 DBMS_SQL.CLOSE_CURSOR(com_cur);
 COMMIT;
 i:=i-1;
 FOR j IN 1..i LOOP
       IF object(j) IS NOT NULL THEN
         DBMS_OUTPUT.PUT_LINE('KEPT: '||object(j));
         DBMS_SHARED_POOL.KEEP(object(j));
       ELSE
         EXIT;
       END IF;
 END LOOP;
END redo_pin;

---

For more information on this topic, I recommend Don Burleson's latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.