понедельник, 16 января 2017 г.

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;

Комментариев нет: