Re: DBMS_SHARED_POOL.KEEP

Bunyamin, I've done something similar.

I have a table with a flag to determine whether to pin objects or not at database startup.

I have a startup trigger owned by sys which is simply this.

begin

        sys.db_startup_proc;
end;

CREATE OR REPLACE PROCEDURE "SYS"."DB_STARTUP_PROC" as

--
	cursor pin_cur is select * from sys.pin_objs;
--
	pin_rec		pin_cur%ROWTYPE;
--
begin
	open pin_cur;
	<<pin_loop>>
	loop
		fetch pin_cur into pin_rec;
		exit pin_loop when pin_cur%NOTFOUND;
--
		if (upper(pin_rec.pin) = 'Y') then
			if (pin_rec.type in ('PROCEDURE','FUNCTION','PACKAGE')) then
				dbms_shared_pool.keep(''|| pin_rec.owner ||'.'|| pin_rec.name
||'');
			elsif (pin_rec.type in ('TRIGGER')) then
			
dbms_shared_pool.keep(''||pin_rec.owner||'.'||pin_rec.name||'',''||'R'||'');
			elsif (pin_rec.type in ('SEQUENCE')) then
			
dbms_shared_pool.keep(''||pin_rec.owner||'.'||pin_rec.name||'',''||'Q'||'');
			end if;
		end if;
--
	end loop pin_loop;
	close pin_cur;
end;

I also have a shutdown trigger and proc but that is outside of what you
are trying to accomplish.  If you want the rest of the code let me know
and I'll send it to you.

- Brian


--- "Bunyamin K. Karadeniz" <bunyamink_at_havelsan.com.tr> wrote:


> Tried and does not work .. 

> 

> 

> Bunyamin K. Karadeniz           

> Oracle DBA / Developer

> Civilian IT Department

> Havelsan A.S. Eskisehir yolu 

> 7.km Ankara Turkey

> Phone: +90 312 2873565 / 1217

> Mobile : +90 535 3357729

> 

> The degree of normality in a database 

> is inversely proportional to that of its DBA.

> 

>   ----- Original Message ----- 

>   From: Mercadante, Thomas F 

>   To: Multiple recipients of list ORACLE-L 

>   Sent: Thursday, April 18, 2002 4:18 PM

>   Subject: RE: DBMS_SHARED_POOL.KEEP

> 

> 

>   Bunyamin,

>    

>   Either try removing the 'EXEC' or try putting a begin and end

> around the call?

>    

>   EXECUTE IMMEDIATE

>
'SYS.DBMS_SHARED_POOL.KEEP('||tab_row.owner||'.'||tab_row.object_name||')';


> 

>   or

> 

>   EXECUTE IMMEDIATE 'BEGIN

>
SYS.DBMS_SHARED_POOL.KEEP('||tab_row.owner||'.'||tab_row.object_name||')'


> || '; END';

>   This is a great idea, by the way!  Let us know how it works!

> 

>   Tom Mercadante 

>   Oracle Certified Professional 

> 

>     -----Original Message-----

>     From: Bunyamin K. Karadeniz [mailto:bunyamink_at_havelsan.com.tr]

>     Sent: Thursday, April 18, 2002 4:28 AM

>     To: Multiple recipients of list ORACLE-L

>     Subject: DBMS_SHARED_POOL.KEEP

> 

> 

>     I want to pin most run packages , I have loeded them into a

> table. For this I have written ,

>      

>     create or replace procedure pin_packages_defined as

>     sql_sentence varchar2(200);

>     cursor_name INTEGER;

>     rows_processed INTEGER;

>     CURSOR tab_cur IS SELECT owner,object_name FROM

> arsiv.pin_aday_objeler;

>     tab_row tab_cur%ROWTYPE;

>     BEGIN

>     FOR tab_row IN tab_cur LOOP

>     --EXECUTE IMMEDIATE 'EXEC

>
SYS.DBMS_SHARED_POOL.KEEP('||tab_row.owner||'.'||tab_row.object_name||')';


> 

>     cursor_name := dbms_sql.open_cursor;

>     sql_sentence

>
:='SYS.DBMS_SHARED_POOL.KEEP('''||tab_row.owner||'.'||tab_row.object_name||''')';


> 

>     dbms_output.put_line(sql_sentence);

>     dbms_sql.parse(cursor_name,sql_sentence, dbms_sql.native);

>     rows_processed := dbms_sql.execute(cursor_name);

>     dbms_sql.close_cursor(cursor_name);

>     END LOOP;

>     END;

> 

>     But , It does not execute , Is it impossible to execute

> DBMS_SHARED_POOL.KEEP dynamically ...I tried DBMS_JOB , It did not

> work too. 

> 

>     How can I do this?

> 

>      

>      

>      

>     Bunyamin K. Karadeniz           

>     Oracle DBA / Developer

>     Civilian IT Department

>     Havelsan A.S. Eskisehir yolu 

>     7.km Ankara Turkey

>     Phone: +90 312 2873565 / 1217

>     Mobile : +90 535 3357729

> 

>     The degree of normality in a database 

>     is inversely proportional to that of its DBA.

> 

> 



__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian Wisniewski
  INET: brian_wisniewski_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).