{"id":14008,"date":"2020-11-02T15:16:02","date_gmt":"2020-11-02T14:16:02","guid":{"rendered":"https:\/\/touk.pl\/blog\/?p=14008"},"modified":"2023-03-16T14:09:44","modified_gmt":"2023-03-16T13:09:44","slug":"how-to-find-all-objects-that-use-dblinks-to-connect-to-remote-servers-using-regular-expressions-on-oracles-all_views","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2020\/11\/02\/how-to-find-all-objects-that-use-dblinks-to-connect-to-remote-servers-using-regular-expressions-on-oracles-all_views\/","title":{"rendered":"How to find all objects that use dblinks"},"content":{"rendered":"<h2 id=\"first\">First<\/h2>\n<p>Create a temporary table as data of dictionary view ALL_VIEWS and convert column TEXT (view text) to CLOB type.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">CREATE TABLE tmp_views AS SELECT a.owner , a.view_name, to_lob(a.text) text FROM all_views a;<\/pre>\n<h2 id=\"second\">Second<\/h2>\n<p>Use regular expression to find definitions with dblinks (using a hierarchical query to display objects used in joins).<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">WITH view_def AS\r\n(--search views use db_link with name \"DB_LINK_NAME\"\r\nSELECT owner,\r\n     view_name,\r\n     text,\r\n     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\r\nFROM tmp_views t\r\nWHERE t.owner IN ('DHP_KENAN_PROD') AND\r\n     regexp_like(upper(text), '\\w+@DB_LINK_NAME', 'i') \r\n),\r\nall_src_obj AS\r\n--this subgery get all use of remote objects \r\n(SELECT owner dh_owner,\r\n     view_name dh_object_name,\r\n     LEVEL lv,\r\n     dbms_lob.substr(upper(regexp_substr(text, '[^ ](\\w+\\.)?(\\$|\\w)+@DB_LINK_NAME', 1, LEVEL, 'i')), --get n''th use of db_link\r\n                     4000, 1) src_obj_call_text --convert from lob to varchar2. One view call can have length 93 char \r\nFROM view_def dt\r\nCONNECT BY nocycle owner = PRIOR owner AND\r\n         view_name = PRIOR view_name AND\r\n         PRIOR sys_guid() IS NOT NULL AND\r\n         LEVEL &lt;= call_cnt --hierarchy level must be less or equel to db_links used in view count\r\n) \r\n--now I parse text with object call to separate columns: src_object_owner and src_object_name\r\nSELECT DISTINCT CASE\r\n              WHEN regexp_like(REPLACE(regexp_substr(src_obj_call_text '.+@', 1, 1),\r\n                                       '@', ''),\r\n                               '(\\w+)\\.((\\$|\\w)+)') THEN\r\n               regexp_replace(REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1),\r\n                                      '@', ''),\r\n                              '(\\w+)\\.((\\$|\\w)+)', '\\1')\r\n              ELSE\r\n               'db_link user'\r\n            END src_object_owner,\r\n            CASE\r\n              WHEN regexp_like(REPLACE(regexp_substr(src_obj_call_text, '.+@',1, 1),\r\n                                       '@', ''),\r\n                               '(\\w+)\\.((\\$|\\w)+)') THEN\r\n               regexp_replace(REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1),\r\n                                      '@', ''),\r\n                              '(\\w+)\\.((\\$|\\w)+)', '\\2')\r\n              ELSE\r\n               REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1), '@', '')\r\n            END src_object_name\r\nFROM all_src_obj;<\/pre>\n<h2 id=\"last\">Last<\/h2>\n<p>Drop temporary table.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">DROP TABLE tmp_views;<\/pre>\n","protected":false},"excerpt":{"rendered":"First Create a temporary table as data of dictionary view ALL_VIEWS and convert column TEXT (view text) to&hellip;\n","protected":false},"author":19,"featured_media":15259,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[687,671,13,676,673,675,674,672],"class_list":{"0":"post-14008","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-development-design","8":"tag-db","9":"tag-hierarchical-query","10":"tag-oracle","11":"tag-oracle-12","12":"tag-regexp","13":"tag-regexp_like","14":"tag-regular-expresion","15":"tag-view"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/14008","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=14008"}],"version-history":[{"count":14,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/14008\/revisions"}],"predecessor-version":[{"id":15275,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/14008\/revisions\/15275"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media\/15259"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=14008"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=14008"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=14008"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}