Oracle切换undo后如何确定使用旧undo的会话信息

背景

是这样的，最近RAC的磁盘组90%报警，存储空间只能再加2T空间。

而这套RAC的DATA组用了normal冗余，不知道当初哪个大聪明这么搞的，底层还是做了RAID 10的。

领导也不知道不懂这么设置的。

因此，这新的2T创建新磁盘组且使用extern的外部冗余，后续将index和undo切换到新的磁盘组上。

index重建到新表空间就行，而undo通过创建新的undo类型表空间并切换新undo达到迁移目的。

参考mos文档：How to Shrink the datafile of Undo Tablespace (Doc ID 268870.1)

上周五创建新undo并且切换过去后，当时立刻直接删除会报：

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

原本想着隔个周末两天时间然后今天周一再次进行删除，没想到还是报错。

根据Undo Tablespace Moved To Pending Switch-Out State (Doc ID 341372.1)可知，当默认的undo表空间改变后，原来使用旧undo的活动事务将在v$rollstat.status中显示为“PENDING OFFLINE”状态。

因此可以使用如下脚本查看使用旧undo的活动事务的会话：

col usn for 999
col name for a25
col USERNAME for a15
col STATUS for a15
col TABLESPACE_NAME for a11
col SID for 99999
col SERIAL# for 99999
col PROGRAM for a19
col MACHINE for a25
col OSUSER for a20
SELECT
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status='PENDING OFFLINE';

以下是我此次查询结果（脱敏）：

db1:
 USN NAME                  STATUS          TABLESPACE ADDR                SID SERIAL# USERNAME        PROGRAM             MACHINE                   OSUSER
---- --------------------- --------------- ---------- ---------------- ------ ------- --------------- ------------------- ------------------------- --------------------
   9 _SYSSMU9_2162248266$  PENDING OFFLINE UNDOTBS1   0000001783BFD9D8   2129      13 ABCD_EFGH       AbcdefghApp.exe     WORKGROUP\VM-ABCD-APP23   abcdefgadmin
  10 _SYSSMU10_3111847501$ PENDING OFFLINE UNDOTBS1   0000001783C44840   2065      13 ABCD_EFGH       AbcdefghApp.exe     WORKGROUP\VM-ABCD-APP23   abcdefgadmin

db2:
 USN NAME                  STATUS          TABLESPACE ADDR                SID SERIAL# USERNAME        PROGRAM             MACHINE                   OSUSER
---- --------------------- --------------- ---------- ---------------- ------ ------- --------------- ------------------- ------------------------- --------------------
  20 _SYSSMU20_2091716515$ PENDING OFFLINE UNDOTBS2   000000177BF53AA0    913       3 ABCD_EFGHG      JDBC Thin Client    ABCD-App08                abcdefgadmin

经过排查发现会话并没有未commit的会话，而是均使用了含有dblink的select查看，因此产生事务。

经确认可以直接kill。

至此，解决。