Sunday, 6 January 2013

Find out invalid objects in Oracle Database and Generate scripts to compile


Find out invalid objects in Oracle and compile them :-

select owner,count(*),object_type from dba_objects where status='INVALID' group by owner,object_type;

select owner,object_name,object_type from dba_objects where status='INVALID' order by owner;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Generate Oracle scripts to compile  procedure , function , package , package body,trigger, view :-


select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME;
===========================================================


query about catalog and catproc :-

select substr(comp_id,1,12) comp_id, status, substr(version,1,10) version, substr(comp_name,1,40) comp_name from dba_registry order by 1;





No comments:

Post a Comment