RE: Error pinning PKS in shared pool

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Mon, 24 Feb 2003 06:48:51 -0800
Message-ID: <F001.005568DE.20030224064851@fatcity.com>

Instead of modifying the trigger all the time, why not just maintain rows in a table?

Here's what I've written after I got tired of making typos that caused the objects after it to fail to pin:

CREATE OR REPLACE TRIGGER sys.qt_pin_on_startup AFTER STARTUP ON DATABASE DECLARE
v_count NUMBER;
CURSOR c1 IS

	SELECT object_owner, object_name, object_type
	FROM qt_dba.objects_to_pin_on_startup
	WHERE valid_object = 'Y'
	FOR UPDATE OF valid_object;

BEGIN


-- 11/08/2002	REJ		Auto-pin these on DB startup.  Run

"$ADMIN/kept_procs.sql" for feedback.
-- NOTE! In order for this to work, GRANT EXECUTE ANY PROCEDURE TO SYS as
well as explicit
-- SELECT access to the QT_DBA.OBJECTS_TO_PIN_ON_STARTUP table *must* be
done!
--

• If the object pulled from the table doesn't exist, this trigger should invalidate the row.
  --
  FOR ocur IN c1 LOOP SELECT COUNT(*) INTO v_count FROM dba_objects WHERE owner = ocur.object_owner AND object_name = ocur.object_name; IF v_count < 1 THEN UPDATE qt_dba.objects_to_pin_on_startup SET valid_object = 'N' WHERE CURRENT OF c1; ELSE EXECUTE IMMEDIATE 'BEGIN sys.dbms_shared_pool.keep('''||ocur.object_owner||'.'||ocur.object_name||''' ,'''||ocur.object_type||'''); END;'; END IF; END LOOP;

        COMMIT; EXCEPTION

	WHEN OTHERS THEN
		COMMIT;

END qt_pin_on_startup;
/

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----

Sent: Monday, February 24, 2003 3:34 AM
To: Multiple recipients of list ORACLE-L

... oops
and then you might want to add that you really have to mess around with quote marks and begin/ends to get it to work - something like (and I really ought to test this before posting, 'cos it's one of those tiny details that there's no point in wasting valuable memorisation time on)

begin

    execute immediate -- no shortened form allowed

        'begin sys.dbms_shared_pool.keep(''DBMS_ALERT'') ; end;'     ;
end;

Note - double up the quotes around the quoted package name, add in the 'begin end' to make the thing you want to execute an anonymous pl/sql block, make sure that there is a semi-colon (which would be incorrect for a pure SQL example) at the end of the thing you are executing.

(I totally agree with your comments though - sys packages have been known to become
mysteriously invalid from time to time).

Regards

Jonathan Lewis
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

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 Mon Feb 24 2003 - 08:48:51 CST