RE: Database Startup Trigger is not pinning all packages

In order to get the startup trigger to fire correctly, you need to grant "EXECUTE ANY PROCEDURE" to the SYS user. Startup and login triggers fire before any role grants take place, so the privileges have to be granted explicitly.

> -----Original Message-----
> From: Mohammad Rafiq [SMTP:rafiq9857_at_hotmail.com]
> Sent: Wednesday, February 07, 2001 12:51 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Database Startup Trigger is not pinning all packages
>
> Why do you want to pin through trigger. Put it into sql file and run it
> as @file_name at startup. For example:
>
> ORACLE_SID=OPRO4; export ORACLE_SID
> . oraenv
> svrmgrl <<!!opro4
> @startup
> !!opro4
> sqlplus -s sys/password @opro4_pin_pkg.sql
>
> Sample file:
> spool opro4_pin_pkg.lst
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.ARP_CASH_RECEIPT_HISTORY');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.ARP_PAY_SCHED');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.ARP_RECEIVABLE_APPLICATIONS');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.ARP_STANDARD');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.FNDCP_CRM');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.FND_CLIENT_INFO');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.FND_GLOBAL');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.FND_INSTALLATION');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.FND_PROFILE');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('APPS.INV_PRJ_CUST_VAL');
> EXECUTE SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD');
> spool off
> Regards
> Rafiq
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Wed, 07 Feb 2001 08:50:30 -0800
>
> Greetings,
>
> I have a database startup trigger which is created from the sys account.
> The trigger is only designed to pin some packages in the shared pool.
> However, the trigger is only pinning the packages that belong to the sys
> account. I am getting the following error in the alert log file when it
> tries to pin packages which belong to other users:
>
> Registered presentation http://admin on database startup
> Error in executing triggers on database startup
> *** 2001-02-07 09:00:48.644
> ksedmp: internal or fatal error
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_UTILITY", line 68
> ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
> ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
> ORA-06512: at line 21
>
> The following is a sample of the trigger:
>
> CREATE OR REPLACE TRIGGER database_startup
> AFTER STARTUP ON DATABASE
>
> BEGIN
>
> sys.dbms_shared_pool.keep('sys.standard');
> sys.dbms_shared_pool.keep('sys.dbms_sys_sql');
> sys.dbms_shared_pool.keep('sys.dbms_sql');
> sys.dbms_shared_pool.keep('sys.dbms_utility');
> sys.dbms_shared_pool.keep('sys.dbms_standard');
> sys.dbms_shared_pool.keep('sys.dbms_output');
> sys.dbms_shared_pool.keep('sys.dbms_pipe');
>
> sys.dbms_shared_pool.keep('ctxsys.driutl');
> sys.dbms_shared_pool.keep('ctxsys.driutl');
>
> sys.dbms_shared_pool.keep('oas_public.htp');
> sys.dbms_shared_pool.keep('oas_public.htp');
> END;
> /
>
> All of the commands in the trigger can be executed successfully when
> logged
> into sqlplus as the sys user with the following syntax:
>
> BEGIN
>
> sys.dbms_shared_pool.keep('oas_public.htp');
> sys.dbms_shared_pool.keep('oas_public.htp');
> . . .
>
> END;
> /
>
> Because of this I am wondering why the trigger errors out.
>
> If anyone can help me I greatly appreciate it. Oracle Support was not
> much
> help.
>
> Thanks,
>
> Lindsay Stoddard
> ACS - GSG
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Lindsay Stoddard
> INET: lindsay.stoddard_at_osha-slc.gov
>
> 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).
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Elliott, Patrick
  INET: Patrick.Elliott_at_bestbuy.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).