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;
