Oracle-L: Re: DBMS_SHARED

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Sat, 20 Apr 2002 14:43:18 -0800
Message-ID: <F001.0044A51D.20020420144318@fatcity.com>

alternately you can put in into a sql file and run/start at sql from statup script. No need for any trigger...File may be added with additional packages for pinning...

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Sat, 20 Apr 2002 13:08:20 -0800

If on 8i you could use a startup trigger for this. Here's mine:

create or replace trigger sys.pin_db_objects after startup on database
begin

     sys.dbms_shared_pool.keep('SYS.DBMSZEXP_SYSPKGGRNT');
     sys.dbms_shared_pool.keep('SYS.DBMS_ALERT');
     sys.dbms_shared_pool.keep('SYS.DBMS_APPLICATION_INFO');
     sys.dbms_shared_pool.keep('SYS.DBMS_AQADM_SYS');
     sys.dbms_shared_pool.keep('SYS.DBMS_AQ_EXP_QUEUES');
     sys.dbms_shared_pool.keep('SYS.DBMS_AQ_IMPORT_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_AQ_SYS_EXP_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_ASYNCRPC_PUSH');
     sys.dbms_shared_pool.keep('SYS.DBMS_DDL');
     sys.dbms_shared_pool.keep('SYS.DBMS_DDL_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFERGEN');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFERGEN_AUDIT');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFERGEN_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFERGEN_LOB');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFERGEN_UTIL');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER_IMPORT_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER_INTERNAL_SYS');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER_QUERY_UTL');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER_SYS');
     sys.dbms_shared_pool.keep('SYS.DBMS_DEFER_SYS_PART1');
     sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE');
     sys.dbms_shared_pool.keep('SYS.DBMS_EXPORT_EXTENSION');
     sys.dbms_shared_pool.keep('SYS.DBMS_IJOB');
     sys.dbms_shared_pool.keep('SYS.DBMS_INTERNAL_TRIGGER');
     sys.dbms_shared_pool.keep('SYS.DBMS_JOB');
     sys.dbms_shared_pool.keep('SYS.DBMS_LOCK');
     sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
     sys.dbms_shared_pool.keep('SYS.DBMS_PRVTRMIE');
     sys.dbms_shared_pool.keep('SYS.DBMS_REFRESH_EXP_LWM');
     sys.dbms_shared_pool.keep('SYS.DBMS_REFRESH_EXP_SITES');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_ADMIN');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_CACHE');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_DECL');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_FLA_UTL');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_MAS');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_RGT_EXP');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_UTL');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_UTL2');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_UTL3');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_UTL4');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_RPC');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPCAT_RPC_UTL');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPUTIL');
     sys.dbms_shared_pool.keep('SYS.DBMS_REPUTIL2');
     sys.dbms_shared_pool.keep('SYS.DBMS_RMGR_GROUP_EXPORT');
     sys.dbms_shared_pool.keep('SYS.DBMS_RMGR_PACT_EXPORT');
     sys.dbms_shared_pool.keep('SYS.DBMS_RMGR_PLAN_EXPORT');
     sys.dbms_shared_pool.keep('SYS.DBMS_SESSION');
     sys.dbms_shared_pool.keep('SYS.DBMS_SNAP_INTERNAL');
     sys.dbms_shared_pool.keep('SYS.DBMS_SQL');
     sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
     sys.dbms_shared_pool.keep('SYS.DBMS_SYSTEM');
     sys.dbms_shared_pool.keep('SYS.DBMS_SYS_SQL');
     sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY');
     sys.dbms_shared_pool.keep('SYS.DBMS_ZHELP');
     sys.dbms_shared_pool.keep('SYS.DBMS_ZHELP_IR');
     sys.dbms_shared_pool.keep('SYS.STANDARD');
     sys.dbms_shared_pool.keep('SYS.UTL_RAW');
     sys.dbms_shared_pool.keep('PERFSTAT.STATSPACK');

end;
/

> "Bunyamin K. Karadeniz" wrote:
>
> 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.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
   INET: lvordos_at_qwest.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).




MOHAMMAD RAFIQ


_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.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).

Received on Sat Apr 20 2002 - 17:43:18 CDT

Re: DBMS_SHARED_POOL.KEEP