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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [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).
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: [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).
