Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. The steps are:
1. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> CREATE UNDO TABLESPACE "APPS_UNDOTS1"
DATAFILE
'+BZPRODDATA' SIZE 16384M ,
'+BZPRODDATA' SIZE 16384M ;
SQL> CREATE UNDO TABLESPACE "APPS_UNDOTS2"
DATAFILE
'+BZPRODDATA' SIZE 16384M ,
'+BZPRODDATA' SIZE 16384M ;
2. Switch to the new Undo tablespace:
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = APPS_UNDOTS1 SCOPE=MEMORY sid='HGPROD1';
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = APPS_UNDOTS2 SCOPE=MEMORY sid='HGPROD2';
3. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.
If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.
SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
SQL> select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='UNDOTBS2' group by tablespace_name, status;
4. If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.
SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
5. Verify and then drop:
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Add Space to the Undo Tablespace
For increasing / resize undo tablespace there are two options :
Resize the existing undo datafile
Add new undo datafile to the tablespace.
引用:https://www.thegeekdiary.com/how-to-resize-the-undo-tablespace-in-oracle-database/
Shrinking Undo Tablespace Size
未经允许不得转载:徐万新之路 » Shrinking Undo Tablespace Size
最新评论
这个牛
放下欲望,男人从来不醉,充分且必要
勇气、责任、自信、创新,为天下先!
软件即数据,软件即服务,软件即管理,软件就是对人类各种社会活动的仿真和记录。软件很重要,但软件不可能凌驾于业务之上,尤其不可能高人一等。