{"id":213,"date":"2017-08-22T23:01:50","date_gmt":"2017-08-22T23:01:50","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=213"},"modified":"2017-08-22T23:01:50","modified_gmt":"2017-08-22T23:01:50","slug":"how-to-resize-undo","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/22\/how-to-resize-undo\/","title":{"rendered":"How to resize Undo"},"content":{"rendered":"<p>You can shrink the datafile of the UNDO tablespace on the primary database to 1G by using the following command:<\/p>\n<pre class=\"lang:default decode:true \" >ALTER DATABASE DATAFILE '+DATA\/datafile\/undotbs01.369.942861921' RESIZE 2048M;<\/pre>\n<p>ASM needs the correct ASM filename. The undo datafile on the standby database will be automatically resized as well. <\/p>\n<p>If you get the following error:<br \/>\nORA-03297: file contains used data beyond requested RESIZE value<\/p>\n<p>There are possibly some extents laying beyond the size you give to resize, so make sure there is nothing in the recycle bin<\/p>\n<pre class=\"lang:default decode:true \" >select * from recyclebin;\r\npurge recyclebin;<\/pre>\n<p>If still not working, try to coalesce the tablespace<\/p>\n<pre class=\"lang:default decode:true \" > SELECT S.tablespace_name,\r\n         s.owner,\r\n         s.segment_name,\r\n         s.segment_type,\r\n         SUM (s.bytes) size_in_bytes,\r\n         ROUND (SUM (s.bytes) \/ 1024 \/ 1024, 2) size_in_m,\r\n         f.file_name\r\n    FROM sys.dba_segments s, sys.dba_data_files f\r\n   WHERE     f.tablespace_name = s.tablespace_name\r\n         AND f.file_id = s.header_file\r\n         AND s.tablespace_name IN ('UNDOTBS01')\r\n         AND f.file_name = '+DATA\/datafile\/undotbs01.369.942861921'\r\nGROUP BY s.tablespace_name,\r\n         s.owner,\r\n         s.segment_name,\r\n         s.segment_type,\r\n         f.file_name\r\nORDER BY s.tablespace_name, s.owner, s.segment_name;\r\n\r\nalter tablespace undo1 coalesce;<\/pre>\n<p>If the above fails, you will have to create a new UNDO tablespace and drop the existing on the primary database:<br \/>\n1. Create a new UNDO tablespace.<\/p>\n<pre class=\"lang:default decode:true \" >CREATE UNDO TABLESPACE undo02 SIZE 1048M;<\/pre>\n<p>For non ASM datafiles you have to specify the datafile:<\/p>\n<pre class=\"lang:default decode:true \" >CREATE UNDO TABLESPACE undo2 DATAFILE '\/databases\/undo02.dbf' SIZE 1024M;\r\nor for ASM\r\nCREATE UNDO TABLESPACE undo2 DATAFILE '+DATA' SIZE 1024M;<\/pre>\n<p>2. Modify the database parameter to use the new UNDO tablespace.<\/p>\n<pre class=\"lang:default decode:true \" >ALTER SYSTEM SET undo_tablespace=undo02 SCOPE=BOTH;\r\nor for RAC\r\nALTER SYSTEM SET undo_tablespace=undo02 SCOPE=BOTH sid='*';<\/pre>\n<p>New transactions will begin using the new undo tablespace. After some time passes (at least the number of seconds specified by the UNDO_RETENTION initialization parameter), you can drop the old UNDO tablespace. This will mean that you will have 2 undo tablespaces for some time and that the new undo tablespace will have a new name.<br \/>\nOn the primary database:<\/p>\n<pre class=\"lang:default decode:true \" >DROP TABLESPACE undo01 INCLUDING CONTENTS AND DATAFILES;<\/pre>\n<p>On the standby you may have to manually delete the datafile of the old undo tablespace.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can shrink the datafile of the UNDO tablespace on the primary database to 1G by using the following command: ALTER DATABASE DATAFILE &#8216;+DATA\/datafile\/undotbs01.369.942861921&#8217; RESIZE 2048M; ASM needs the correct ASM filename. The undo datafile on the standby database will be automatically resized as well. If you get the following error: ORA-03297: file contains used &#8230; <a title=\"How to resize Undo\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/22\/how-to-resize-undo\/\" aria-label=\"Read more about How to resize Undo\">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":[57,52],"class_list":["post-213","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-resize","tag-undo"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/213","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=213"}],"version-history":[{"count":2,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"predecessor-version":[{"id":215,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/213\/revisions\/215"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}