{"id":77,"date":"2022-11-09T15:23:27","date_gmt":"2022-11-09T18:23:27","guid":{"rendered":"https:\/\/kappasoft.com.br\/wiki\/?p=77"},"modified":"2022-11-09T15:25:47","modified_gmt":"2022-11-09T18:25:47","slug":"como-verificar-dblinks-com-base-nos-codigos-das-materialized-views","status":"publish","type":"post","link":"https:\/\/kappasoft.com.br\/wiki\/2022\/11\/09\/como-verificar-dblinks-com-base-nos-codigos-das-materialized-views\/","title":{"rendered":"Como verificar DBLINKS com base nos c\u00f3digos dos objetos (procedure, package, package body, trigger, function, type, library, type body, views e materialized views)"},"content":{"rendered":"\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre class=\"CodeMirror\" data-setting=\"{&quot;showPanel&quot;:true,&quot;languageLabel&quot;:&quot;language&quot;,&quot;fullScreenButton&quot;:true,&quot;copyButton&quot;:true,&quot;mode&quot;:&quot;sql&quot;,&quot;mime&quot;:&quot;text\/x-sql&quot;,&quot;theme&quot;:&quot;material&quot;,&quot;lineNumbers&quot;:false,&quot;styleActiveLine&quot;:false,&quot;lineWrapping&quot;:false,&quot;readOnly&quot;:true,&quot;fileName&quot;:&quot;&quot;,&quot;language&quot;:&quot;SQL&quot;,&quot;maxHeight&quot;:&quot;400px&quot;,&quot;modeName&quot;:&quot;sql&quot;}\">-- VERIFICANDO OS DBLINKS COM BASE NO C\u00d3DIGO DOS OBJETOS (PROCEDURE, PACKAGE, PACKAGE BODY, TRIGGER, FUNCTION, TYPE, LIBRARY, TYPE BODY)\n\nSELECT OWNER, NAME, TYPE \nFROM DBA_SOURCE\nWHERE 1=1\nAND TEXT LIKE '%@%'\nAND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG')\nGROUP BY OWNER, NAME, TYPE\nORDER BY OWNER;\n\n-- VERIFICANDO OS DBLINKS COM BASE NO C\u00d3DIGO DA VIEW\n\nSELECT OWNER, VIEW_NAME, 'DROP VIEW '|| OWNER ||'.'|| VIEW_NAME ||';' FROM ALL_VIEWS\nWHERE 1=1\nAND TEXT_VC LIKE '%@%'\nAND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG')\nGROUP BY OWNER, VIEW_NAME\nORDER BY OWNER;\n\n-- VERIFICANDO OS DBLINKS COM BASE NO C\u00d3DIGO DA MAT. VIEW\n\nCREATE TABLE SCOTT.ALL_MVIEW_TMP AS \nSELECT OWNER, MVIEW_NAME, TO_LOB(QUERY) TEXTO \nFROM ALL_MVIEWS \nWHERE 1=1\nAND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG');\n\nSELECT OWNER, MVIEW_NAME, 'DROP VIEW '|| OWNER ||'.'|| MVIEW_NAME ||';' FROM SCOTT.ALL_MVIEW_TMP\nWHERE 1=1\nAND TEXTO LIKE '%@%'\nAND OWNER IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG')\nGROUP BY OWNER, MVIEW_NAME\nORDER BY OWNER;\n\nDROP TABLE SCOTT.ALL_MVIEW_TMP;\n\n-- REGERAR AS ESTAT\u00cdSTICAS POR ESQUEMAS\n\nSELECT 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''|| USERNAME ||''',CASCADE=&gt;TRUE);' FROM DBA_USERS \nWHERE 1=1\nAND USERNAME IN ('CNEFEATUAD','CNEFEUNIV','GERINS','SGMCNEFE','CNEFECETE','CNEFEDGC','BET','BOG','BOG2005','BOG2010','BOG2010A','BOG2010A3','BETCONS','BETXBOG');\n<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,2],"tags":[],"class_list":["post-77","post","type-post","status-publish","format-standard","hentry","category-banco-de-dados","category-oracle"],"_links":{"self":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/77","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/comments?post=77"}],"version-history":[{"count":3,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/77\/revisions"}],"predecessor-version":[{"id":81,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/77\/revisions\/81"}],"wp:attachment":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/media?parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/categories?post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/tags?post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}