Invalid Objects

[aarroyob]

Hi all,

I little question for your. You know sometimes in the database are invalid objects, and when you make alter .... compile; this objects continue invalid.

My question:

Does anybody know where Oracle store the sql code definition for this invalid objects?

Thanks and best regards

Angel

[dknight]

What type of objects are you asking about?

[pando]

sometimes the order of compilation affects the status of the object, dependencies and such

[aarroyob]

Originally posted by dknight
What type of objects are you asking about?

In this case I have invalid views.

[pando]

well look dba_views for view definitions

[aarroyob]

Originally posted by pando
sometimes the order of compilation affects the status of the object, dependencies and such

Yes Pando I know, normally I compile invalid objects 2/3 times with:

select 'ALTER ' || OBJECT_TYPE || ' '||OWNER||'.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE <> 'PACKAGE BODY'
UNION
select 'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE BODY;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'PACKAGE BODY';

[pando]

well that script doesnt compile the objects in dependency tree order does it...

[julian]

Originally posted by aarroyob
Hi all,

I little question for your. You know sometimes in the database are invalid objects, and when you make alter .... compile; this objects continue invalid.

My question:

Does anybody know where Oracle store the sql code definition for this invalid objects?

Thanks and best regards

Angel

PROCEDURE RECOMPILE IS
cur Number;
I Number;
sqlpac_cur integer;
begin
I := 0;
loop -- infinite
select curnt(*) into cur
from User_Objects
where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY','PACKAGE','VIEW')
and object_name!='RECOMPILE'
and STATUS = 'INVALID';
If cur = 0 OR I > 10 then return; end if;
I := I +1;
for FPCur in (select distinct object_name,object_type
from user_objects
where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY','PACKAGE','VIEW')
and object_name!='RECOMPILE'
and STATUS = 'INVALID'
order by object_type desc
) Loop
begin
sqlpac_cur:=dbms_sql.open_cursor;
if FPCur.Object_type = 'PACKAGE BODY' then FPCur.Object_type := 'PACKAGE'; end if;
dbms_sql.parse(sqlpac_cur,'alter '||FPCur.Object_type||' '||FPCur.object_name||' compile',dbms_sql.v7);
dbms_output.put_line('processed: '||FPCur.Object_type||' '||FPCur.object_name);
dbms_sql.close_cursor(sqlpac_cur);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(sqlpac_cur);
dbms_output.put_line('failed: '||FPCur.object_type||' '||FPCur.object_name||' '||sqlerrm);
end;
end loop;
end loop;

end;

[pando]

well utlrp.sql does that trick but doesnt follow the dependency tree neither

[reydp]

I also just have to recompile invalid objects more than once.
Do you guys have script to identify the dependency first before
the creation of the script to recompile invalid objects?

If such script is available, then compilation of invalid objects will only be done once, and that's great :-))