RE: Invalid Package under SYS
- From: "Rudy Zung" <Rudy.Zung@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Apr 2004 12:59:25 -0400
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------
Other related posts: