{"id":327,"date":"2018-08-01T12:59:29","date_gmt":"2018-08-01T02:59:29","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=327"},"modified":"2018-08-01T12:59:29","modified_gmt":"2018-08-01T02:59:29","slug":"how-to-monitor-an-undo-transaction","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2018\/08\/01\/how-to-monitor-an-undo-transaction\/","title":{"rendered":"How to Monitor an Undo Transaction"},"content":{"rendered":"<p>We can monitor the progress of an undo operation by running the query shown below:<\/p>\n<pre class=\"lang:default decode:true \" >select session.username\r\n, substr(session.program, 1, 19) command\r\n, transaction.used_ublk\r\n, from v$session session\r\n, v$transaction transaction\r\nwhere session.saddr = transaction.ses_addr;\r\n<\/pre>\n<p>From the first session connected as SH we issue a DELETE statement<\/p>\n<pre class=\"lang:default decode:true \" >SQL&gt; conn customer\/customer123\r\nConnected.\r\nSQL&gt; delete * from customer;<\/pre>\n<p>While the delete process is in progress, we can monitor the usage of undo blocks from another session. As user SYS we issue the SQL statement shown above and we see that the USED_UBLK column value keeps increasing as the delete statement progresses and more undo blocks are generated.<\/p>\n<pre class=\"lang:default decode:true \" >SQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER         sqlplus@its41007(TN       11070\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER         sqlplus@its41007(TN       11212\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER        sqlplus@its41007(TN       15996\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER        sqlplus@its41007(TN       20246<\/pre>\n<p>After the delete operation is completed, we now run a ROLLBACK command to undo the delete operation.<br \/>\nWhile the rollback is in operation, running the same query shows that the USED_UBLK column now instead keeps decreasing in value until the query returns &#8216;no rows selected&#8217;.<\/p>\n<pre class=\"lang:default decode:true \" >SQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER        sqlplus@its41007(TN        3389\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER        sqlplus@its41007(TN        3376\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER        sqlplus@its41007(TN        2409\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nSH              sqlplus@its41007(TN        1344\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER         sqlplus@its41007(TN         775\r\nSQL&gt; \/\r\nUSERNAME        COMMAND               USED_UBLK\r\n--------------- -------------------- ----------\r\nCUSTOMER        sqlplus@its41007(TN         399\r\nSQL&gt; \/\r\nno rows selected<\/pre>\n<p>At this point we can confirm that the user CUSTOMER would have completed the rollback operation.<\/p>\n<pre class=\"lang:default decode:true \" >SQL&gt; rollback;\r\nRollback complete.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>We can monitor the progress of an undo operation by running the query shown below: select session.username , substr(session.program, 1, 19) command , transaction.used_ublk , from v$session session , v$transaction transaction where session.saddr = transaction.ses_addr; From the first session connected as SH we issue a DELETE statement SQL&gt; conn customer\/customer123 Connected. SQL&gt; delete * from &#8230; <a title=\"How to Monitor an Undo Transaction\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2018\/08\/01\/how-to-monitor-an-undo-transaction\/\" aria-label=\"Read more about How to Monitor an Undo Transaction\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-327","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=327"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/327\/revisions"}],"predecessor-version":[{"id":328,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/327\/revisions\/328"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=327"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}