Oracle Find Invalid Objects (Packages, Views, Procedures, Functions...)

Working on database refreshes can be very time consuming to detect invalid objects when Oracle Database Links have been changed. 

The one that causes the most frustration is looking for Oracle Views that have an invalid database link, because the database link is stores as a LONG type. The queries below will find all the invalid objects including views and provide a list of objects to recompile.  I have a uses  global recompile script and will post at a later time. This will search objects looking for either invalid objects or specific values (such as database links in views) 
--Get Invalid Objects
select distinct o.OWNER, o.OBJECT_TYPE, o.OBJECT_NAME
from  dba_objects o
where o.OWNER in ('XXXX','XXXXX')
and status != 'VALID'
order by 1;

--Look for any code that has DB Link of name XXXXXX
select *
from DBA_Source s
where lower(s.text) like '%XXXXXX%'

--VIEWS TWO OPTIONS 1: Dump to XLS or 2: Run a Cursor

-- 1. Export to Excel and search the XLS Doc
select v.owner, v.VIEW_NAME, v.text
from DBA_Views v
where OWNER in ('XXXXX','XXXXX')
order by 1,2;

 -- 2. Run Cursor to DBMS Dump
     cursor v_cur is select * from all_views where owner != 'SYS';
   for v_rec in v_cur
       if (instr(lower(v_rec.text), 'XXXXX') > 0) then
            dbms_output.put_line('Match found in ' || v_rec.owner || '.' || v_rec.view_name);
       end if;
   end loop;

Comments are closed