正德厚生,臻于至善

Shrinking Undo Tablespace Size

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/
赞(0) 打赏
未经允许不得转载:徐万新之路 » Shrinking Undo Tablespace Size

支持快讯、专题、百度收录推送、人机验证、多级分类筛选器,适用于垂直站点、科技博客、个人站,扁平化设计、简洁白色、超多功能配置、会员中心、直达链接、文章图片弹窗、自动缩略图等...

联系我们

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续提供更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫

微信扫一扫

登录

找回密码

注册