Re: DBMS_SHARED_POOL.KEEP

Thank you for all answers ,

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 ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, April 18, 2002 6:18 PM

> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bunyamin K. Karadeniz
  INET: bunyamink_at_havelsan.com.tr

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).