How to find all objects that use dblinks

First

Create a 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;

Second

Use regular expression to find definitions with dblinks (using a 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;

Last

Drop temporary table.

DROP TABLE tmp_views;