Como verificar DBLINKS com base nos códigos dos objetos (procedure, package, package body, trigger, function, type, library, type body, views e materialized views)

-- VERIFICANDO OS DBLINKS COM BASE NO CÓDIGO DOS OBJETOS (PROCEDURE, PACKAGE, PACKAGE BODY, TRIGGER, FUNCTION, TYPE, LIBRARY, TYPE BODY)

SELECT OWNER, NAME, TYPE 
FROM DBA_SOURCE
WHERE 1=1
AND TEXT LIKE '%@%'
AND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG')
GROUP BY OWNER, NAME, TYPE
ORDER BY OWNER;

-- VERIFICANDO OS DBLINKS COM BASE NO CÓDIGO DA VIEW

SELECT OWNER, VIEW_NAME, 'DROP VIEW '|| OWNER ||'.'|| VIEW_NAME ||';' FROM ALL_VIEWS
WHERE 1=1
AND TEXT_VC LIKE '%@%'
AND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG')
GROUP BY OWNER, VIEW_NAME
ORDER BY OWNER;

-- VERIFICANDO OS DBLINKS COM BASE NO CÓDIGO DA MAT. VIEW

CREATE TABLE SCOTT.ALL_MVIEW_TMP AS 
SELECT OWNER, MVIEW_NAME, TO_LOB(QUERY) TEXTO 
FROM ALL_MVIEWS 
WHERE 1=1
AND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG');

SELECT OWNER, MVIEW_NAME, 'DROP VIEW '|| OWNER ||'.'|| MVIEW_NAME ||';' FROM SCOTT.ALL_MVIEW_TMP
WHERE 1=1
AND TEXTO LIKE '%@%'
AND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG')
GROUP BY OWNER, MVIEW_NAME
ORDER BY OWNER;

DROP TABLE SCOTT.ALL_MVIEW_TMP;

-- REGERAR AS ESTATÍSTICAS POR ESQUEMAS

SELECT 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''|| USERNAME ||''',CASCADE=>TRUE);' FROM DBA_USERS 
WHERE 1=1
AND USERNAME IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG');