• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

PROC_PKGS_KEEP

everytime we do the release in our db, one procedure from SYS schema becomes invalid.

SYS.PROC_PKGS_KEEP

not sure what is this process, can someone pls tell me why this is happening and what is the use of this procedure and what if it is in invalid status, what'r the implications.

Thanks -
0
ram_0218
Asked:
ram_0218
  • 2
  • 2
2 Solutions
 
abuckheitCommented:
read through this and you should be able to resolve the issue:


Checked for relevance on 13-Jun-2007


PURPOSE
  This note explains how to automatically pin the most often loaded procedures
  and packages in the shared pool at database startup.


SCOPE & APPLICATION
  This example uses the 'AFTER STARTUP ON DATABASE' and 'BEFORE SHUTDOWN ON
  DATABASE' triggers to automate this administrative task.


How to Automate Pinning Objects in the Shared Pool at Database Startup:
=======================================================================

********************************************************
1/ Create the procedures to be executed by the triggers
********************************************************

a. Create a table to store the names of packages and procedures that had to be
   reloaded several times during the instance life.

   SQL> create table sys.list_tab (owner varchar2(64),NAME VARCHAR2(100));
   Table created.

b. The procedure proc_pkgs_list retrieves the names of the packages and
   procedures that will be kept in the shared pool at startup and inserts
   the names in the table.
create or replace PROCEDURE proc_pkgs_list AS pragma AUTONOMOUS_TRANSACTION;
        own varchar2(64);
   SQL> create or replace PROCEDURE proc_pkgs_list AS
     2         pragma AUTONOMOUS_TRANSACTION;
     3         own varchar2(64);
     4         nam varchar2(100);
     5      cursor pkgs is
     6         select owner,name
     7         from SYS.v_$db_object_cache
     8         where type in ('PACKAGE','PROCEDURE')
     9         and   (loads > 1 or KEPT='YES');
    10  BEGIN
    11        delete from sys.list_tab;
    12        commit;
    13         open pkgs;
    14         loop
    15         fetch pkgs into own, nam;
    16         exit when pkgs%notfound;
    17       insert into sys.list_tab values (own , nam);
    18    commit;
    19    end loop;
    20    end;
    21  /

   Procedure created.
 
c. The procedure proc_pkgs_keep retrieves the procedures and package names and
   keeps the objects in the shared pool. In order to use the dbms_shared_pool
   package procedures, execute the dbmspool.sql script first.

   SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
     2   own varchar2(64);
     3   nam varchar2(100);
     4   cursor pkgs is
     5   select owner ,name
     6   from sys.list_tab;
     7  BEGIN
     8   open pkgs;
     9   loop
    10   fetch pkgs into own, nam;
    11   exit when pkgs%notfound;
    12   SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
    13   end loop;
    14   sys.dbms_shared_pool.keep('SYS.STANDARD');
    15   sys.dbms_shared_pool.keep('SYS.DIUTIL');
    16  END;
    17  /

   Procedure created.

********************************************************
2/ Test that the procedures execute properly
********************************************************

   SQL> execute sys.proc_pkgs_list;
   PL/SQL procedure successfully completed.

   SQL> execute sys.proc_pkgs_keep;
   PL/SQL procedure successfully completed.

********************************************************
3/ Create the event triggers
********************************************************

a. The procedure proc_pkgs_list is executed before the
   database shuts down, since this is the only way to get
   the list of the procedures and packages that need to be
   kept in the shared pool. For this purpose, create a
   trigger to be fired before the instance shuts down.
   
   SQL> CREATE OR REPLACE TRIGGER db_shutdown_list
     2   BEFORE SHUTDOWN ON DATABASE
     3  BEGIN
     4   sys.proc_pkgs_list;
     5  END;
     6  /

   Trigger created.

b. The procedure proc_pkgs_keep is executed when the database
   starts up. For this purpose, create a trigger to be fired
   after the database starts up.
 
   SQL> CREATE OR REPLACE TRIGGER db_startup_keep
     2   AFTER STARTUP ON DATABASE
     3  BEGIN
     4   sys.proc_pkgs_keep;
     5  END;
     6  /

   Trigger created.


Errors
------

Check in the alert.log to see if the database triggers executed successfully.

When shutting down or starting up a database, if a database trigger fails to
execute, the following messages may appear in the alert.log:

*** SHUTDOWN

Shutting down instance (immediate)
License high water mark = 2
Mon May 22 12:31:45 2000
ALTER DATABASE CLOSE NORMAL
Mon May 22 12:31:45 2000
SMON: disabling tx recovery
Mon May 22 12:31:46 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_12624.trc:
ORA-04098: trigger 'DB_SHUTDOWN_LIST' is invalid and failed re-validation
SMON: disabling cache recovery
Mon May 22 12:31:47 2000
Thread 1 closed at log sequence 16579
Mon May 22 12:31:47 2000
Completed: ALTER DATABASE CLOSE NORMAL
Mon May 22 12:31:47 2000
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT


*** STARTUP
Example 1:

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
...
SMON: enabling tx recovery
Tue Apr 18 10:21:38 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_7291.trc:
ORA-04098: trigger 'DB_STARTUP_KEEP' is invalid and failed re-valid
ation
Tue Apr 18 10:21:38 2000
Completed: alter database open

Tue Apr 18 10:21:30 2000
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0

Example 2:

SMON: enabling tx recovery
Tue Apr 18 11:12:41 2000
Errors in file /8i/ora815/admin/hp11_815/udump/ora_7562.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 43
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51
ORA-06512: at "SYS.PROC_PKGS_KEEP", line 13
ORA-06512: at line 2
Tue Apr 18 11:12:41 2000
Completed: alter database open

In the /8i/ora815/admin/hp11_815/udump/ora_7562.trc file:

Error in executing triggers on STARTUP
*** 2000.04.18.11.12.41.052
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 43
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51
ORA-06512: at "SYS.PROC_PKGS_KEEP", line 13
ORA-06512: at line 2

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

Procedures and triggers are created and executed under SYS. The table list_tab
must exist before the database is shutdown the first time.
Execute the procedures before shutting down the database to test the triggers.
0
 
johnsoneSenior Oracle DBACommented:
I do not see any reference to that procedure in my 9i or 10g installations.  You should not be putting anything under SYS.

The implication if it is invalid, is that Oracle will attempt to recompile it the next time it is called, assuming there are no errors, there should be no problems.

I would check in DBA_DEPENDENCIES to see what it is dependent on.  It must be dependent on at least one of the application objects you applied.  My guess is it is a custom procedure and should be removed from the SYS schema.
0
 
abuckheitCommented:
my post explains why it would be in there, someone added it at some point in time for the reasons stated in the article
0
 
johnsoneSenior Oracle DBACommented:
I was researching it while you were posting.  I didn't see your post before I put mine in.

I would still contend that it shouldn't be owned by SYS as you never know if Oracle will create their own object with that name.

We always pin packages in the script we use to startup the database.  Less moving parts.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now