如何优雅的删除undo表空间

前言
因磁盘空间不足，需要将undo表空间迁移到其它的存储空间
本文介绍如何优雅的删除undo表空间，并在新的存储空间中创建新的undo表空间

详细操作步骤如下：

1、查看默认undo表空间

SQL>show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

2、查看undo表空间使用情况

SQL>set linesize 1000
SQL>set pagesize 1000
SQL>col usagep for a10
SQL>col tablespace_name for a20
SQL>select ff.s tablespace_name,
    ff.b total,
    (ff.b - fr.b) usage,
    fr.b free,
    round((ff.b-fr.b) / ff.b * 100) || '%' usagep
      from (select tablespace_name s, sum(bytes) / 1024 / 1024 b
        from dba_data_files
        group by tablespace_name) ff,
            (select  tablespace_name s, sum(bytes) / 1024 / 1024 b
        from dba_free_space
        group by tablespace_name) fr
    where ff.s = fr.s order by round ((ff.b - fr.b) / ff.b * 100);
TABLESPACE_NAME           TOTAL      USAGE       FREE USAGEP
-------------------- ---------- ---------- ---------- ----------
UNDOTBS                   18200    70.9375 18129.0625 0%

3、查看undo表空间数据文件

SQL>select file_id,file_name,tablespace_name,status,autoextensible,bytes/1024/1024 MB from dba_data_files;
   FILE_ID FILE_NAME                                                    TABLESPACE_NAME      STATUS    AUTOEXTENSIBLE          MB
---------- ------------------------------------------------------------ -------------------- --------- --------------- ----------
34 +DATA/orcl/datafile/undotbs.273.1079005483               UNDOTBS              AVAILABLE YES                  18200

4、创建新的undo表空间

SQL>create undo tablespace undotbs1 datafile '+DATA' size 100m autoextend on;
Tablespace created.

5、设置默认的undo表空间

SQL>alter system set undo_tablespace=UNDOTBS1 scope=both;
System altered.

6、查看修改后的undo表空间

SQL>show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

7、告警日志提示

Undo Tablespace 48 moved to Pending Switch-Out state.
表示正在迁移undo数据到新的undo表空间
当出现**Undo Tablespace 48 successfully switched out.**表示迁移完成，可以删除旧的undo表空间了。
查看正在使用旧的undo表空间：未选择表示没有正在使用，可以删除旧的undo表空间。

SQL>select * from dba_rollback_segs where tablespace_name='UNDOTBS' and status='ONLINE';
no rows selected

8、删除旧的undo表空间

SQL>drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

9、undo表空间迁移过程监控

9.1、查看正在使用的undo段

SQL>select segment_name,owner,tablespace_name,status
from dba_rollback_segs
where tablespace_name = 'UNDOTBS' and status = 'ONLINE';

9.2、查看哪个用户在使用undo段

SQL>select s.username,s.status,s.sid, u.name
  from v$transaction t, v$rollstat r, v$rollname u, v$session s
 where s.taddr = t.addr
   and t.xidusn = r.usn
   and r.usn = u.usn
 order by s.username;

9.3、查看使用旧的undo表空间的状态是否有online状态

SQL>select * from dba_rollback_segs where tablespace_name='UNDOTBS' and status='ONLINE';
SEGMENT_NAME                   OWNER  TABLESPACE_NAME      SEGMENT_ID    FILE_ID   BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
------------------------------ ------ -------------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------
_SYSSMU129_1627357485$         PUBLIC UNDOTBS                     129         34        272         131072       65536           2       32765              ONLINE

9.4、编写正在占用undo表空间的kill语句

SQL>SELECT 'alter system kill session '''||
       s.sid|| ','||
       s.serial#|| ''''||';'
  FROM v$session         s,
       v$transaction     t,
       v$rollname        r,
       v$rollstat        g,
       dba_rollback_segs h,
       v$sqlarea i
 WHERE t.addr = s.taddr
   AND t.xidusn = r.usn
   AND r.usn = g.usn
   and r.name = h.segment_name
   AND s.PREV_SQL_ID=i.SQL_ID and h.tablespace_name='UNDOTBS' and s.status='INACTIVE' ;