RE: Invalid Package under SYS

From: Rudy Zung <Rudy.Zung_at_efi.com>
Date: Tue, 27 Apr 2004 12:59:25 -0400
Message-ID: <4FBAA533C7C64940A7921F3000736B12E444CC@pghexmb01.printcafe.efi.internal>

I use the following script to handle this situation for me.

Might be a good idea to take a cold backup of your database for just in = case.

Run the script as SYS.

...Rudy
=3D=3D=3D=3D=3D=3D=3Dbegin script revalidate.sql
-- name: $Id: revalidate.sql,v 1.15 2004/01/06 18:19:15 zungr Exp $

• usage: revalidates objects that have incidentally become invalidated,
• but in reality should still be valid
• use: call
• pre: dbms_output should be accessible and valid because we may use
• it to generate feedback for the caller
• post: as long as no cycles are detected, all objects owned by the
• caller should be revalidated. if a cycle is detected then an
• exception is immediately raised
• imp: implementation of a depth-first, dependency-aware Oracle object
• recompiler with cycle detection.
• wow, that sounded impressive.
• would be nicer if we could do this recursively, but anonymous
• blocks can't recurse and we don't have lambda forms in Oracle,
• so we implement this procedurally
• bugs: does not handle compiling all object types yet.
• kind of slow, but it was the only way I could think of to make
• sure that it performs correctly, and doesn't go into an = infinite
• loop. if an object is recompiled by another session while this
• script is running, the script may incorrectly flag cycles=20
• based on the LAST_DDL_TIME when in fact no cycles may exist
• history:
• Thu Jun 29 15:03:51 2000, Rudy Zung on ZUNGR
• Created
• Fri Jun 27 13:18:32 2003, Rudy Zung on PCAF1241
• Rewritten to be schema agnostic and no longer rely on custom
• knowledge of how our schema is laid out
• Wed Oct 15 14:52:54 2003, Rudy Zung on PCAF1241
• Based on an insight from Jean Kuzniar, this is a near complete
• rewrite to change from depth-first traversal to width-first
• traversal. In doing so, we no longer have to walk down each
• object's dependancy chain prior to recompiling it; now all we
• care about is the bottom-most layer of the dependency chain
• representing the set of invalid objects which are ready for
• immediate recompilation because the objects don't depend on
• anything that is invalid

set feedback off
set timing off

exec dbms_output.disable
set serveroutput off
set serveroutput on
exec dbms_output.enable(2000000)

declare

   type LIST_TVC is table of varchar2(128);    X_CYCLE exception;
   N_CYCLE constant number :=3D -20991;    pragma exception_init(X_CYCLE, -20991);    M_CYCLE constant varchar2(80) :=3D 'Cycle detected';

   objectStack LIST_TVC :=3D LIST_TVC();

   depth         number;
   compiled      number;
   totalCompiled number;
   invalids      number;
   totalInvalids number;
   startTime     number;
   ddlTime       number;
   position      number;
   object        varchar2(128);
   marker        varchar2(48);

   objectType USER_OBJECTS.OBJECT_TYPE%type;    compileType varchar2(16);
begin

   select to_char(sysdate, 'J.sssss')

      into startTime
      from USER_USERS;
                                /* get a count of invalid objects; this
                                 * will help us later in cycle detection
                                 */
   select count(*)
      into totalInvalids
      from USER_OBJECTS
      where STATUS <> 'VALID';

   select count(*)
      into invalids
      from (select distinct
                   OBJECT_NAME,
                   decode(OBJECT_TYPE,
                          'PACKAGE BODY', 'PACKAGE',
                          'TYPE BODY'   , 'TYPE',
                          OBJECT_TYPE)
               from USER_OBJECTS
               where STATUS <> 'VALID');

   dbms_application_info.SET_ACTION('Compile');
   depth         :=3D 0;
   compiled      :=3D -1;

   totalCompiled :=3D 0;
   while (compiled <> 0)
   loop

      depth    :=3D depth + 1;
      compiled :=3D 0;
      for tCursor in=20
         (select /*+ RULE */
                 OBJECT_NAME,
                 OBJECT_TYPE
             from USER_OBJECTS
             where STATUS <> 'VALID' and
                   (user <> 'SYS' or
                    OBJECT_NAME not in
                       (-- _NEXT_OBJECT showing up in USER_OBJECTS is=20
                        -- Oracle bug 691329, fixed in 817
                        '_NEXT_OBJECT',
                        '_default_auditing_options_',
                        'DBMS_OUTPUT'))
          minus
          select /*+ RULE */
                 ad.NAME,
                 ad.TYPE
             from ALL_OBJECTS      ao,
                  ALL_DEPENDENCIES ad
             where ao.STATUS     <> 'VALID'             and
                   ao.OBJECT_TYPE =3D ad.REFERENCED_TYPE  and
                   ao.OBJECT_NAME =3D ad.REFERENCED_NAME  and
                   ao.OWNER       =3D ad.REFERENCED_OWNER and
                   ao.OWNER       =3D ad.OWNER            and
                   ad.OWNER       =3D user)
      loop
         dbms_application_info.SET_CLIENT_INFO(
            totalCompiled + 1 || '/' || invalids || '@' || depth || ': ' =
||
            tCursor.OBJECT_NAME);
         object :=3D tCursor.OBJECT_TYPE || '.' || tCursor.OBJECT_NAME;

         if (totalCompiled >=3D totalInvalids) then
                                /* we're compiling more objects than =
were
                                 * originally invalid; now is the time =
to
                                 * do a more rigorous check for cycles
                                 */
            begin


--                                /* see if the object's last_ddl_time

--                                 * is after this script started. if

--                                 * it is, then it's a strong =

indication


--                                 * that this script has just =

recompiled


--                                 * the object and if we have to =

recompile


--                                 * it again, it would mean that =

there's


--                                 * a cycle in the dependency chain

--                                 */

--               select to_char(LAST_DDL_TIME,

--                              'J.sssss')

--                  into ddlTime

--                  from USER_OBJECTS

--                  where OBJECT_TYPE =3D tCursor.OBJECT_TYPE and

--                        OBJECT_NAME =3D tCursor.OBJECT_NAME;

--               if (ddlTime > startTime) then

                                /* make a more thorough attempt to =
determine
                                 * if there was indeed a cycle by =
inspecting
                                 * the stack
                                 */
                  if (nvl(objectStack.COUNT, 0) > 0) then
                     for position in objectStack.FIRST .. =
objectStack.LAST
                     loop
                        if (objectStack(position) =3D object) then
                           raise_application_error(N_CYCLE,
                                                   M_CYCLE || ': ' ||
                                                   object);
                        end if;
                     end loop;
                  end if;


--               end if; -- if (ddlTime > startTime)


            exception
               when X_CYCLE then
                  for position in objectStack.FIRST .. objectStack.LAST
                  loop
                     if (objectStack(position) <> object) then
                        marker :=3D '';
                     else
                        marker :=3D ' <<<<<<< <<<<<<< <<<<<<<';
                     end if;
                     dbms_output.PUT_LINE(
                        to_char(position, 'FM00000') || '=3D' ||
                        objectStack(position) || marker);
                  end loop;
                  dbms_output.PUT_LINE('Top=3D' || tCursor.OBJECT_NAME);
                  raise;
            end;
         end if;

         objectStack.EXTEND;
         objectStack(objectStack.COUNT) :=3D object;

         objectType  :=3D tCursor.OBJECT_TYPE;
         compileType :=3D '';

         if (objectType =3D 'PACKAGE BODY') then
            objectType  :=3D 'PACKAGE';
            compileType :=3D 'BODY';
         elsif (objectType =3D 'TYPE BODY') then
            objectType  :=3D 'TYPE';
            compileType :=3D 'BODY';
         end if;

         begin
            execute immediate
                'alter ' || objectType || ' ' ||
                tCursor.OBJECT_NAME    || ' ' ||
                trim('compile' || ' ' || compileType);
         exception
            when X_CYCLE then
               raise;

            when others then
               dbms_output.put_line(replace(objectType,
                                            ' ') || '.' ||
                                    tCursor.OBJECT_NAME || ': ' ||
                                    SQLERRM);
         end;
         compiled      :=3D compiled      + 1;
         totalCompiled :=3D totalCompiled + 1;
      end loop; -- for tCursor in (...)
   end loop; -- while (compiled <> 0)
                                /* adjust the depth because we always =
loop
                                 * once more than necessary for a check =
to
                                 * make sure that we didn't have to =
compile
                                 * anything
                                 */

   depth :=3D depth - 1;

   dbms_application_info.SET_ACTION('');    dbms_application_info.SET_CLIENT_INFO('');

   if (totalCompiled =3D 0) then

      dbms_output.PUT_LINE('Nothing invalid');    else

      dbms_output.PUT_LINE('Recompiled ' || totalCompiled  ||=20
                           ' object'     ||
                           substr('s',
                                  1,
                                  sign(totalCompiled - 1)) ||
                           ' in '        || depth          ||
                           ' pass'       ||
                           substr('es',
                                  1,
                                  sign(depth - 1) * 3));

   end if;
end;
/

set echo off
set pagesize 66
set feedback on
set heading on
set pagesize 32000
set timing off

select OBJECT_TYPE,
       OBJECT_NAME
    from USER_OBJECTS=20
    where
          (user <> 'SYS' or
           OBJECT_NAME not in (-- _NEXT_OBJECT showing up in =
USER_OBJECTS=20
                               -- is Oracle bug 691329, fixed in 817
                               '_NEXT_OBJECT',
                               '_default_auditing_options_')) and
          STATUS <> 'VALID'

/
=3D=3D=3D=3D=3D=3D=3Dend script

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hamid Alavi
Sent: Monday, April 26, 2004 4:59 PM
To: 'Oracle-L (E-mail)
Subject: Invalid Package under SYS

I have run the utlirp under the sys account BUT Still I can see some packages are invalid.
Any Idea or solution?

Thanks,

Hamid Alavi

Office : 818-737-0526
Cell phone : 818-416-5095

---

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

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Apr 27 2004 - 12:22:37 CDT