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" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
