Oracle UNDO TABLESPACE 100%占用不释放解决办法

Oracle Undo段中区3种状态（DBA_UNDO_EXTENTS的STATUS列）：ACTIVE、EXPIRED和UNEXPIRED：
ACTIVE即未提交的Undo信息（活动）：表示事物还在活动，该值对应的Undo段的DBA_ROLLBACK_SEGS.STATUS一定是ONLINE状态，一旦没有活动的事务在使用Undo段，那么对应的Undo段就变成OFFLINE状态。ACTIVE状态的Undo区不会被覆盖。
EXPIRED即过期的Undo信息（过期）：表示事务已经提交且超过了UNDO_RETENTION指定时间，该状态可以被覆盖使用。
UNEXPIRED即提交的Undo信息（未过期）：表示事务已经提交但是还没有超过UNDO_RETENTION指定时间，该状态可以被覆盖使用。

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;

set lines 200 pages 50000
col name for a40
col value for a10
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 where x.indx = y.indx
   AND x.ksppinm in ('_highthreshold_undoretention'，'_undo_autotune');

NAME                                     VALUE      DESCRIB
---------------------------------------- ---------- ------------------------------------------------------------
_highthreshold_undoretention             4294967294 high threshold undo_retention in seconds #方法1:限制undoretention最大时间，就是在自动调整的结果上强制限制最大时间（试了效果不大）
_undo_autotune                           TRUE       enable auto tuning of undo_retention     #方法2：直接关闭autotune（这个更干脆）

show parameter undo
set lines 200 pages 50000
col name for a40
col value for a10
col describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 where x.indx = y.indx
   AND x.ksppinm in ('_highthreshold_undoretention'，'_undo_autotune');

undo_retention=43200
_highthreshold_undoretention=43200

NAME                                     VALUE      DESCRIB
---------------------------------------- ---------- ------------------------------------------------------------
_highthreshold_undoretention             4294967294 high threshold undo_retention in seconds #方法1:限制undoretention最大时间，就是在自动调整的结果上强制限制最大时间（试了效果不大）
_undo_autotune                           TRUE       enable auto tuning of undo_retention     #方法2：直接关闭autotune（这个更干脆）

--alter system set "_undo_autotune"=false scope=spfile sid='*';
alter system set "_highthreshold_undoretention"=86400 scope=memory sid='*';
alter system set undo_retention=86400 scope=memory sid='*';

alter system set "_highthreshold_undoretention"=86400 scope=both sid='*';
alter system set undo_retention=86400 scope=both sid='*';
########################################APPS_UNDOTS切换成UNDOTBS，生产环境谨慎操作，直接粘贴出事概不负责
create UNDO tablespace UNDOTBS1 datafile '+BZPRODDATA' size 20G;
create UNDO tablespace UNDOTBS2 datafile '+BZPRODDATA' size 20G;

alter tablespace UNDOTBS1 add datafile '+BZPRODDATA' size 20G;
alter tablespace UNDOTBS2 add datafile '+BZPRODDATA' size 20G;

alter system set undo_tablespace=UNDOTBS1 sid='BZPRODDATA1' scope=memory;
alter system set undo_tablespace=UNDOTBS2 sid='BZPRODDATA2' scope=memory;

select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='APPS_UNDOTS1';
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='APPS_UNDOTS2';
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='APPS_UNDOTS1' group by tablespace_name, status;
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='APPS_UNDOTS2' group by tablespace_name, status;

drop tablespace APPS_UNDOTS1 including contents and datafiles;
drop tablespace APPS_UNDOTS2 including contents and datafiles;

########################################UNDOTBS切换成APPS_UNDOTS，生产环境谨慎操作，直接粘贴出事概不负责
CREATE UNDO TABLESPACE "APPS_UNDOTS1"
BZPRODDATAFILE
        '+BZPRODDATA' SIZE 20G ,
        '+BZPRODDATA' SIZE 20G ;

CREATE UNDO TABLESPACE "APPS_UNDOTS2"
BZPRODDATAFILE
        '+BZPRODDATA' SIZE 20G ,
        '+BZPRODDATA' SIZE 20G ;

alter system set undo_tablespace=APPS_UNDOTS1 sid='BZPRODDATA1' scope=memory;
alter system set undo_tablespace=APPS_UNDOTS2 sid='BZPRODDATA2' scope=memory;

select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';
select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS2';
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='UNDOTBS1' group by tablespace_name, status;
select tablespace_name, status, count(*) from dba_rollback_segs where tablespace_name='UNDOTBS2' group by tablespace_name, status;

drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS2 including contents and datafiles;
########################################How To Set Specific Undo Tablespace For Each PDB Instance On RAC Node
On node-1:
alter session set container=BZPROD;
alter system set undo_tablespace=APPS_UNDOTS1 container=current sid='BZCDB1' scope=both;             >>>> sid=First Instance
On Node-2
alter session set container=BZPROD;
alter system set undo_tablespace=APPS_UNDOTS2 container=current sid='BZCDB2' scope=both;             >>>> sid=Second Instance
#################################################################################

--select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;
--select property_name, property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
--select dr.tablespace_name, dr.segment_name, vr.status from dba_rollback_segs dr, v$rollstat vr where dr.segment_id=vr.usn;

Primary Note: High Undo Space Usage (Doc ID 1578639.1)
Automatic Tuning of Undo Retention Common Issues (Doc ID 1579779.1)
With Local Undo Enabled and RAC Environment,The UNDO Tablespace of a PDB is UNDO_2 (Doc ID 2971554.1)
With Local Undo enabled, How To Set Specific Undo Tablespace For Each PDB Instance On RAC Node (Doc ID 2673826.1)