How to find all objects that use dblinks to connect to remote servers (using regular expressions on Oracle’s all_views)

  1. First – create temporary table as data of dictionary view ALL_VIEWS and convert column TEXT (view text) to CLOB type

    CREATE TABLE tmp_views AS SELECT a.owner , a.view_name, to_lob(a.text) text FROM all_views a;
    
  2. Second – use regular expression to find to find definitions with dblinks (using hierarchical query to display objects used in joins)

    WITH view_def AS
    (--search views use db_link with name "DB_LINK_NAME"
    SELECT owner,
         view_name,
         text,
         upper(regexp_count(text, '[^ ](\w+\.)?(\$|\w)+@DB_LINK_NAME', 1, 'i')) call_cnt --this is count of remote objects in view definition. I need it to build hierarchy
    FROM tmp_views t
    WHERE t.owner IN ('DHP_KENAN_PROD') AND
         regexp_like(upper(text), '\w+@DB_LINK_NAME', 'i') 
    ),
    all_src_obj AS
    --this subgery get all use of remote objects 
    (SELECT owner dh_owner,
         view_name dh_object_name,
         LEVEL lv,
         dbms_lob.substr(upper(regexp_substr(text, '[^ ](\w+\.)?(\$|\w)+@DB_LINK_NAME', 1, LEVEL, 'i')), --get n''th use of db_link
                         4000, 1) src_obj_call_text --convert from lob to varchar2. One view call can have length 93 char 
    FROM view_def dt
    CONNECT BY nocycle owner = PRIOR owner AND
             view_name = PRIOR view_name AND
             PRIOR sys_guid() IS NOT NULL AND
             LEVEL <= call_cnt --hierarchy level must be less or equel to db_links used in view count
    ) 
    --now I parse text with object call to separate columns: src_object_owner and src_object_name
    SELECT DISTINCT CASE
                  WHEN regexp_like(REPLACE(regexp_substr(src_obj_call_text '.+@', 1, 1),
                                           '@', ''),
                                   '(\w+)\.((\$|\w)+)') THEN
                   regexp_replace(REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1),
                                          '@', ''),
                                  '(\w+)\.((\$|\w)+)', '\1')
                  ELSE
                   'db_link user'
                END src_object_owner,
                CASE
                  WHEN regexp_like(REPLACE(regexp_substr(src_obj_call_text, '.+@',1, 1),
                                           '@', ''),
                                   '(\w+)\.((\$|\w)+)') THEN
                   regexp_replace(REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1),
                                          '@', ''),
                                  '(\w+)\.((\$|\w)+)', '\2')
                  ELSE
                   REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1), '@', '')
                END src_object_name
    FROM all_src_obj;
    
  3. Last – drop temporary table

    DROP TABLE tmp_views;
    

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.