{"id":78,"date":"2017-02-13T10:49:33","date_gmt":"2017-02-13T09:49:33","guid":{"rendered":"https:\/\/blog.kurschies.de\/?p=78"},"modified":"2017-02-23T07:20:07","modified_gmt":"2017-02-23T06:20:07","slug":"alter-table-shrink-space-and-its-progress","status":"publish","type":"post","link":"https:\/\/blog.kurschies.de\/index.php\/2017\/02\/13\/alter-table-shrink-space-and-its-progress\/","title":{"rendered":"alter table &#8230; shrink space &#8211; and its progress"},"content":{"rendered":"<p>well, there are a lot of good tutorials regarding when and how to use the &#8220;alter table &#8230; shrink space&#8221; command &#8211; and don&#8217;t panic, I won&#8217;t do another one \ud83d\ude09<\/p>\n<p>just a few words to mechanism behind: the shrink is done in 2 steps<\/p>\n<ol>\n<li>the rows from the &#8220;end&#8221; of a table will be moved to the &#8220;beginning&#8221;. Internally, this is done by &#8220;select &#8211; insert &#8211; delete&#8221; statements, row by row. So there are only locks on row level and a lot of small transactions. Therefore, this part is completely online and has just a small performance impact on running transactions (I&#8217;ve seen somewhat about 2% and 10%, depending on your application)<\/li>\n<li>The &#8220;high water mark&#8221; will be reset to the latest block of the table. Oracle has to lock the table exclusively &#8211; so it&#8217;s not really online.<\/li>\n<\/ol>\n<p>and a short summary about the different &#8220;shrink space&#8221; commands (because I always forget them myself)<\/p>\n<ul>\n<li>alter table &#8230; shrinc space:\n<ul>\n<li>do both steps successive<\/li>\n<\/ul>\n<\/li>\n<li>alter table &#8230; shrink space cascade:\n<ul>\n<li>do both steps successive for the table and all related objects like indexes and lobs<\/li>\n<li>if you want to see all related objects, just execute this:\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nselect * from (table(DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS('&lt;OWNER&gt;','&lt;TABLE&gt;',null, 1)));\r\n<\/pre>\n<\/li>\n<\/ul>\n<\/li>\n<li>alter table &#8230; shrink space compact:\n<ul>\n<li>do only step 1<\/li>\n<\/ul>\n<\/li>\n<li>alter table &#8230; modify lob (&#8230;) (shrink space):\n<ul>\n<li>do step 1 &amp; 2 for lob column<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>And now: the tricky part&#8230; how to minitor progress&#8230; and just the short answer at the beginning: it&#8217;s not possible. If you think of using v$session_longops &#8211; as I mentioned above, there many <em>short<\/em> transactions&#8230;<\/p>\n<p>but there are some other indications you can use to check progress, although you can&#8217;t calculate the end time properly&#8230; nevertheless it&#8217;s enough to see the process is ongoing \ud83d\ude09<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nselect\r\n   b.sid\r\n  ,a.event\r\n  ,a.WAIT_TIME\r\n  ,round(d.read_value\/1024\/1024\/1024,2) read_gb\r\n  ,round(d.write_value\/1024\/1024\/1024,2) write_gb\r\n  ,round(d.undo_value\/1024\/1024\/1024,2) undo_gb\r\n  ,e.current_undo\r\n  ,c.SQL_TEXT\r\nfrom v$session_wait a\r\njoin v$session b on a.sid = b.sid\r\njoin v$sqlarea c on b.SQL_ID = c.SQL_ID\r\njoin (select * from (select s.sid, t.name, s.value from v$sesstat s inner join v$statname t on s.statistic#=t.statistic#)\r\n              pivot (sum(value) as value for name in (\r\n                 'undo change vector size' UNDO\r\n                ,'physical read total bytes' READ\r\n                ,'physical write total bytes' WRITE\r\n))) d on a.sid=d.sid\r\njoin (select b.sid, sum(a.USED_UBLK) current_undo from V$TRANSACTION a join v$session b on a.SES_ADDR=b.saddr group by b.sid) e on e.sid=a.sid\r\nwhere upper(c.sql_text) like 'ALTER TABLE%SHRINK SPACE%'\r\nand b.sid != (select sys_context('USERENV','SID') from dual)\r\n;\r\n<\/pre>\n<p>Just to explain the output:<\/p>\n<ul>\n<li>SID: the user SID executing the shrink command<\/li>\n<li>EVENT: the current wait event&#8230; until its &#8220;direct path read&#8221; or &#8220;db file &#8230; read&#8221; you are in step 1<\/li>\n<li>WAIT_TIME: should be nearly 0, since we have just small transactions.<\/li>\n<li>READ_GB: amount of data the session has read vom disk<\/li>\n<li>WRITE_GB: amount of data the session has written to disk<\/li>\n<li>UNDO_GB: amount of data the session has written to undo<\/li>\n<li>CURRENT_UNDO: number of undo blocks currently used&#8230; should be very low (&lt;20) &#8211; see &#8220;small transactions&#8221;<\/li>\n<li>SQL_TEXT: the SQL text we are monitoring&#8230;<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>well, there are a lot of good tutorials regarding when and how to use the &#8220;alter table &#8230; shrink space&#8221; command &#8211; and don&#8217;t panic, I won&#8217;t do another one \ud83d\ude09 just a few words to mechanism behind: the shrink &hellip; <a href=\"https:\/\/blog.kurschies.de\/index.php\/2017\/02\/13\/alter-table-shrink-space-and-its-progress\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1],"tags":[7],"class_list":["post-78","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-oracle"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8m6YC-1g","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts\/78","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/comments?post=78"}],"version-history":[{"count":25,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts\/78\/revisions"}],"predecessor-version":[{"id":104,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/posts\/78\/revisions\/104"}],"wp:attachment":[{"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/media?parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/categories?post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.kurschies.de\/index.php\/wp-json\/wp\/v2\/tags?post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}