Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBMS_SHARED_POOL.KEEP

Re: DBMS_SHARED_POOL.KEEP

From: Suzy Vordos <lvordos_at_qwest.com>
Date: Sat, 20 Apr 2002 13:08:20 -0800
Message-ID: <F001.0044A4F7.20020420130820@fatcity.com>

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).
Received on Sat Apr 20 2002 - 16:08:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US