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 o.OBJECT_TYPE in ('PACKAGE','PACKAGE BODY','TRIGGER','VIEW','PROCEDURE','FUNCTION')
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
--------------------------------------------------
declare
cursor v_cur is select * from all_views where owner != 'SYS';
begin
for v_rec in v_cur
loop
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;
end;