Get objects with dblinks

Script to get objects with dblinks in source code. It checks views, materialized views and objects with source code (I don’t check through dba_dependencies because it returns objects that use dblinks via synonyms).
It can be easily extended for dba_jobs for example.

Version with all_ views

WITH db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM all_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM all_views
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM all_mviews
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM all_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM all_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;

Version with dba_ views

WITH owner_list(owner) AS (
  SELECT USER FROM dual
 ),
 db_links(owner, db_link, username, host) as (
    SELECT owner, RTRIM(replace(UPPER(db_link), UPPER(SYS_CONTEXT('USERENV', 'DB_DOMAIN'))), '.') db_link, username, 
      nvl(REGEXP_REPLACE(host, '.*HOST\s*=\s*(.+?)\).*PORT\s*=\s*(.+?)\).*(SID|SERVICE_NAME)\s*=\s*(.+?)\).*', '\1:\2/\4', 1, 1, 'in'), host) host
    FROM dba_db_links
   ),
 vw AS (SELECT /*+ no_merge*/
   owner,
   view_name,
   dbms_metadata.get_ddl('VIEW', view_name, owner) txt
  FROM dba_views
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
 mat_vw AS (SELECT /*+ no_merge*/
   owner,
   mview_name,
   dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name, owner) txt
  FROM dba_mviews
  where owner IN (SELECT owner FROM owner_list)
  ORDER BY 1, 2),
all_obj AS
   (SELECT 'VIEW' obj_type, vw.owner owner, vw.view_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT 'MATERIALIZED_VIEW' obj_type, mat_vw.owner owner, mat_vw.mview_name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM db_links d, mat_vw
    WHERE regexp_like(txt, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
    UNION
    SELECT REPLACE(s.type, ' ', '_') obj_type, s.owner owner, s.name obj_name, d.db_link, d.owner db_link_owner, username, host
    FROM dba_source s, db_links d 
    WHERE regexp_like(s.text, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND s.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB_PROGRAM' obj_type, j.owner, program_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_programs j, db_links d
    WHERE program_type = 'PLSQL_BLOCK'
      AND regexp_like(program_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    UNION
    SELECT 'SCHEDULER_JOB' obj_type, j.owner, job_name AS obj_name, db_link, d.owner db_link_owner, username, host
    FROM dba_scheduler_jobs j, db_links d
    WHERE job_type = 'PLSQL_BLOCK'
      AND regexp_like(job_action, '@' || d.db_link || '([^A-Za-z0-9#$_]|$)', 'i')
      AND j.owner IN (SELECT owner FROM owner_list)
    ORDER BY 2, 1, 3)
SELECT *
FROM (
  SELECT o.*, 
    row_number() OVER (PARTITION BY  o.owner, o.obj_type, o.obj_name ORDER BY case WHEN db_link_owner = o.owner THEN 1 ELSE 2 END) rn  
  FROM all_obj o
  WHERE o.db_link_owner = owner OR o.db_link_owner = 'PUBLIC'
  )
WHERE rn = 1
ORDER BY owner, db_link, obj_name;