REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_sga_pined_plsql.sql
REM
REM <SQLDIR_GRP>SGA</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports PINED PLSQL objects.
REM	--
REM	Will ask you if you want to KEEP these objects
REM	if you say yes make sure you have this
REM	package installed:
REM	   SYS.DBMS_SHARED_POOL.KEEP
REM	You can install it by running DBMSPOOL.SQL as SYS.
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_sga_pined_plsql.sql
REM 
REM Example:
REM	s_sga_pined_plsql.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

ttitle off
btitle off

set lines 132
col obj_name format a45
break on type skip 1

select type
,      owner||'.'||name   obj_name
,      kept
from   v$db_object_cache
where  type = 'PACKAGE'
or     type = 'PACKAGE BODY'
or     type = 'PROCEDURE'
or     type = 'TRIGGER'
or     type = 'FUNCTION'
-- or     type = 'TABLE'
order by type, owner, name
/


prompt Do you want to keep all these objects in the SGA?
prompt Enter - Yes
prompt Ctl-c - No
accept dummy prompt "===> "
prompt If you want to keep these objects in SGA
prompt you need to run DBMSPOOL.SQL as SYS
prompt have you done that? If not press Ctl-C
prompt Enter - Yes
prompt Ctl-c - No
accept dummy prompt "===> "


set term off
set feedback off
set pages 0
set trims on

select 'EXECUTE sys.dbms_shared_pool.keep('''||owner||'.'||name||''');'
from   v$db_object_cache
where  type = 'PACKAGE'
or     type = 'PACKAGE BODY'
or     type = 'PROCEDURE'
-- or     type = 'TRIGGER'
or     type = 'FUNCTION'
-- or     type = 'TABLE'
order by type, owner, name

spool keep.run
/
spool off



set term on
set feedback on

prompt Are you sure you want to keep these objects in SGA
prompt Enter - Yes
prompt Ctl-c - No
accept dummy prompt "===> "

@keep.run


prompt Done !

ttitle off
btitle off

set lines 132
col obj_name format a45
break on type skip 1

select type
,      owner||'.'||name   obj_name
,      kept
from   v$db_object_cache
where  type = 'PACKAGE'
or     type = 'PACKAGE BODY'
or     type = 'PROCEDURE'
or     type = 'TRIGGER'
or     type = 'FUNCTION'
-- or     type = 'TABLE'
order by type, owner, name
/