keep_gc.sql

REM 	AUTHOR : Alok Verma
REM	PURPOSE: This utility will PIN all procedures/
REM              and packages in SGA
prompt *******                                     ********
prompt ******* Pinning packages, procedures in SGA ********
prompt *******                                     ********
--
set serveroutput on size 1000000
declare
my_own varchar2(15);
my_nam varchar2(50);
my_typ varchar2(10);
my_status varchar2(10);
cur INTEGER;	-- holds cursor ID
ret INTEGER;	-- holds call return value
str varchar2(100);
cursor pkgs is
	select owner, object_name, object_type, status
	from all_objects
	where object_type in ('PACKAGE','PROCEDURE')
	and (owner like 'SYS'
	 or owner like 'TOV%'
	 or owner like 'JT%'
	 or owner like 'POV%'
	 or owner like 'JP%')
	order by owner;
begin
	open pkgs;
	loop
		fetch pkgs into my_own, my_nam, my_typ, my_status;
		exit when pkgs%notfound;
		dbms_output.put_line('');
		if my_own = 'SYS'
		then if my_nam in ('DBMS_OUTPUT','DBMS_PIPE','STANDARD',
				'DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL',
				'DBMS_SHARED_POOL','DBMS_UTILITY')
		     then sys.dbms_shared_pool.keep(my_own||'.'||my_nam);
			  dbms_output.put('Pinned '|| my_own||'.'||my_nam);
		     end if;
		else sys.dbms_shared_pool.keep(my_own||'.'||my_nam);
		     dbms_output.put('Pinned '|| my_own||'.'||my_nam);
		end if;
		begin
		if my_typ = 'PROCEDURE' and  my_status = 'INVALID' 
		then 
			str := 'ALTER PROCEDURE '||my_own||'.'||my_nam||
			' COMPILE'; 
			dbms_output.put('. Invalid PROCEDURE, trying to compile...');
			cur := dbms_sql.open_cursor;
			dbms_sql.parse(cur,str,dbms_sql.v7);
			ret := dbms_sql.execute(cur);
			dbms_sql.close_cursor(cur);
			dbms_output.put('Compiled');
		elsif my_typ = 'PACKAGE'
		then 	select count(*) into cur FROM all_objects WHERE 
			object_type='PACKAGE BODY' and object_name=my_nam and owner=my_own
			and status = 'INVALID';
			if cur != 0
			then
				str := 'ALTER PACKAGE '||my_own||'.'||my_nam||
				' COMPILE'; 
				dbms_output.put('. Invalid PACKAGE BODY, trying to compile...');
				cur := dbms_sql.open_cursor;
				dbms_sql.parse(cur,str,dbms_sql.v7);
				ret := dbms_sql.execute(cur);
				dbms_sql.close_cursor(cur);
				dbms_output.put('Compiled');
			end if;
		end if;


		exception
		when others then
			dbms_output.put_line('Error in compiling '||my_own||'.'||my_nam);
			dbms_output.put_line(SQLERRM);
		end;
	end loop;
end;
/
@ckgc