{"id":2018,"date":"2021-01-21T12:15:07","date_gmt":"2021-01-21T12:15:07","guid":{"rendered":"https:\/\/swiv.com.br\/enabling-trace-for-a-specific-oracle-schema\/"},"modified":"2026-05-27T20:02:53","modified_gmt":"2026-05-27T19:02:53","slug":"enabling-trace-for-a-specific-oracle-schema","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/01\/21\/enabling-trace-for-a-specific-oracle-schema\/","title":{"rendered":"Enabling trace for a specific oracle schema"},"content":{"rendered":"\n<p>Para aquelas necessidades pontuais de habilitar o trace para todas as sess\u00f5es provenientes de um schema espec\u00edfico, podemos criar uma trigger no banco de dados conforme abaixo. Devo registrar aqui os cr\u00e9ditos ao Ricardo Portilho Proni, que gentilmente mandou este script para me ajudar com um caso do dia a dia.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SET DEFINE OFF\nSQL&gt; CREATE OR REPLACE TRIGGER SET_TRACE AFTER LOGON ON DATABASE\n  2  BEGIN\n  3  IF USER IN (&#039;BSS&#039;) THEN\n  4  EXECUTE IMMEDIATE &#039;ALTER SESSION SET TRACEFILE_IDENTIFIER=&#039;&#039;TRACE_BSS&#039;&#039;&#039;;\n  5  EXECUTE IMMEDIATE &#039;ALTER SESSION SET TIMED_STATISTICS=TRUE&#039;;\n  6  EXECUTE IMMEDIATE &#039;ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED&#039;;\n  7  EXECUTE IMMEDIATE &#039;ALTER SESSION SET EVENTS &#039;&#039;10046 TRACE NAME CONTEXT FOREVER, LEVEL 12&#039;&#039;&#039;;\n  8  END IF;\n  9  END;\n 10  \/\n \nGatilho criado.\n \nSQL&gt;\n<\/pre><\/div>\n\n\n<p>Depois disso, quando o banco receber novas conex\u00f5es do schema BSS, vai gerar os arquivos traces conforme exemplo abaixo:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n-rw-r----- 1 user group 192K Jan 21 09:09 orcl_ora_69850_TRACE_BSS.trm\n-rw-r----- 1 user group 1.2M Jan 21 09:09 orcl_ora_69850_TRACE_BSS.trc\n-rw-r----- 1 user group  969 Jan 21 09:10 orcl_ora_71293_TRACE_BSS.trm\n-rw-r----- 1 user group 1.5K Jan 21 09:10 orcl_ora_71293_TRACE_BSS.trc\n-rw-r----- 1 user group  63M Jan 21 09:10 alert_orcl.log\n-rw-r----- 1 user group 1016 Jan 21 09:10 orcl_ora_71284_TRACE_BSS.trm\n-rw-r----- 1 user group 1.7K Jan 21 09:10 orcl_ora_71284_TRACE_BSS.trc\n&#x5B;user@oel7 trace]$\n<\/pre><\/div>\n\n\n<p>Ap\u00f3s isso, \u00e9 s\u00f3 realizar o TKPROF utilizando os par\u00e2metros pertinentes para a situa\u00e7\u00e3o.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Para aquelas necessidades pontuais de habilitar o trace para todas as sess\u00f5es provenientes de um schema espec\u00edfico, podemos criar uma trigger no banco de dados conforme abaixo. Devo registrar aqui os cr\u00e9ditos ao Ricardo Portilho Proni, que gentilmente mandou este script para me ajudar com um caso do dia a dia. Depois disso, quando o [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[19],"class_list":["post-2018","post","type-post","status-publish","format-standard","hentry","category-administration","tag-oracle-database"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=2018"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2018\/revisions"}],"predecessor-version":[{"id":9278,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2018\/revisions\/9278"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=2018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=2018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=2018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}