{"id":404,"date":"2014-05-01T09:28:58","date_gmt":"2014-05-01T07:28:58","guid":{"rendered":"http:\/\/www.rocworks.at\/wordpress\/?p=404"},"modified":"2014-05-15T16:50:31","modified_gmt":"2014-05-15T14:50:31","slug":"oracle-12c-global-temporary-tables-undo-redo","status":"publish","type":"post","link":"https:\/\/www.rocworks.at\/wordpress\/?p=404","title":{"rendered":"Oracle 12c Global Temporary Tables &#038; Undo &#038; Redo&#8230;"},"content":{"rendered":"<p>Oracle-Magazin: <a href=\"http:\/\/www.oracle.com\/technetwork\/issue-archive\/2013\/13-nov\/o63asktom-2034271.html\">http:\/\/www.oracle.com\/technetwork\/issue-archive\/2013\/13-nov\/o63asktom-2034271.html<\/a><\/p>\n<p>Starting in Oracle Database 12c, temporary undo can be stored in the temporary tablespace and undo for permanent objects can be stored in the undo tablespace. What this effectively means is that operations on temporary tables will <strong>no longer generate redo<\/strong>. <\/p>\n<p>How undo is generated in Oracle Database 12c for global temporary tables is controlled by a new init.ora parameter: temp_undo_enabled. It has two settings: TRUE and FALSE. By default, this parameter is set to FALSE and undo will be generated in the same fashion it was in the past. For example:<\/p>\n<p><code>SQL> alter session<br \/>\nset temp_undo_enabled = false;<\/p>\n<p>Session altered.<\/p>\n<p>SQL> insert into gtt<br \/>\n  2  select *<br \/>\n  3    from all_objects;<br \/>\n87310 rows created.<\/p>\n<p>Statistics<br \/>\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<br \/>\n\u2026<br \/>\n     566304  redo size<br \/>\n\u2026<\/p>\n<p>SQ> update gtt<br \/>\n  2     set object_name =<br \/>\n        lower(object_name);<br \/>\n87310 rows updated.<\/p>\n<p>Statistics<br \/>\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<br \/>\n\u2026<br \/>\n    8243680  redo size<br \/>\n\u2026 <\/p>\n<p>As you can see, the INSERT generates about half a megabyte of redo (566,304 bytes) while the UPDATE generates upwards of 8 MB of redo (8,243,680 bytes). If I enable temporary undo, however:<\/p>\n<p>SQL> alter session<br \/>\nset temp_undo_enabled = true;<\/p>\n<p>Session altered.<\/p>\n<p>SQL> insert into gtt<br \/>\n  2  select *<br \/>\n  3    from all_objects;<br \/>\n87310 rows created.<\/p>\n<p>Statistics<br \/>\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<br \/>\n\u2026<br \/>\n        280  redo size<br \/>\n\u2026<\/p>\n<p>SQL> update gtt<br \/>\n  2     set object_name =<br \/>\n        lower(object_name);<br \/>\n87310 rows updated.<br \/>\nStatistics<br \/>\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014<br \/>\n\u2026<br \/>\n          0  redo size<br \/>\n\u2026 <\/p>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle-Magazin: http:\/\/www.oracle.com\/technetwork\/issue-archive\/2013\/13-nov\/o63asktom-2034271.html Starting in Oracle Database 12c, temporary undo can be stored in the temporary tablespace and undo for permanent objects can be stored in the undo tablespace. What this effectively means is that operations on temporary tables will no &hellip; <a href=\"https:\/\/www.rocworks.at\/wordpress\/?p=404\">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":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-404","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=404"}],"version-history":[{"count":5,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/404\/revisions"}],"predecessor-version":[{"id":428,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/404\/revisions\/428"}],"wp:attachment":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}