今天早上登录系统检查后台日志的时候发现大量的：Undo Tablespace 4 moved to Pending Switch-Out state。上周五下午的时候我执行了生成库的UNDO切换，当时也出现了这些日志。baidu了以下，提示出是一些事务还在用老的UNDO,时间一过就OK了 当时并没在意。可以两天了那些事务还悬挂在上面 让我感觉到有点奇怪，按照下面的步骤解决这个问题：
SQL> select a.usn,d.addr,e.sid,e.serial# from v$rollname a,v$rollstat b,v$transaction d,v$session e where a.usn=d.xidusn and d.addr=e.taddr and b.status='PENDING OFFLINE';
USN ADDR SID SERIAL#
---------- ---------------- ---------- ----------
39 07000004C8DA80A0 3202 37979
39 07000004C8DA80A0 3202 37979
56 07000004C8C2D130 3150 24699
56 07000004C8C2D130 3151 55487
56 07000004C8C2D130 3150 24699
56 07000004C8C2D130 3151 55487
6 rows selected.
SQL> SELECT D.SPID,B.SID,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
2 FROM V$SESSION B,V$SQLAREA C,V$PROCESS D
3 WHERE B.SQL_HASH_VALUE=C.HASH_VALUE AND B.PADDR=D.ADDR
4 AND B.SID IN(3202,3150,3151);
SPID SID SQL_TEXT
------------------------ ---------- ------------------------------------------------------------------------------------------------
594040 3150 SELECT "TQPC","ZT","ALOGWJM","SJGSDWS","CZR" FROM "DM_TMP_TQQK" "B" WHERE ("ZT"='6' OR "ZT"='7')
1159562 3151 SELECT "A1"."SJSJ","A1"."SJSJ","A1"."RWID","A1"."RWZT","A1"."SJGSDWDM","A1"."TQPC","A2"."SJGSDWD
SQL> select addr,xidusn,status,start_time from v$transaction;
ADDR XIDUSN STATUS START_TIME
---------------- ---------- ---------------- --------------------
07000004C8C2D130 56 ACTIVE 12/06/10 17:07:09
07000004C8DA80A0 39 ACTIVE 12/21/10 10:40:32
SQL> !ps -ef|grep 594040
oracle 594040 1 0 Dec 06 - 0:58 oraclenciis4 (LOCAL=NO)
oracle 692464 356488 0 10:27:19 pts/1 0:00 grep 594040
SQL> !ps -ef|grep 1159562
oracle 233714 356488 0 10:27:30 pts/1 0:00 grep 1159562
oracle 1159562 1 0 Dec 06 - 0:04 oraclenciis4 (LOCAL=NO)
SQL> select s.username, 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;
USERNAME NAME
------------------------------ ------------------------------
NCIIC _SYSSMU39_1242356781$
ZX_RES _SYSSMU56_1251965650$
ZX_TMP _SYSSMU56_1251965650$
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
0 0 ONLINE .000358582 .000358582 0
2179 0 ONLINE .007804871 .007804871 0
56 1 PENDING OFFLINE .007926941 1.89073944 27
2187 0 ONLINE .008781433 .008781433 0
39 1 PENDING OFFLINE .062614441 3.99816132 220
2188 0 ONLINE 2.791008 3.978508 0
40 rows selected.
SQL> !kill -9 594040
SQL> !kill -9 1159562
SQL> select addr,xidusn,status,start_time from v$transaction;
ADDR XIDUSN STATUS START_TIME
---------------- ---------- ---------------- --------------------
07000004C8DA80A0 39 ACTIVE 12/21/10 10:40:32
SQL> select addr,xidusn,status,start_time from v$transaction;
ADDR XIDUSN STATUS START_TIME
---------------- ---------- ---------------- --------------------
07000004C8DA80A0 39 ACTIVE 12/21/10 10:40:32
SQL> /
ADDR XIDUSN STATUS START_TIME
---------------- ---------- ---------------- --------------------
07000004C8DA80A0 39 ACTIVE 12/21/10 10:40:32
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
0 0 ONLINE .000358582 .000358582 0
2170 0 ONLINE .003898621 .003898621 0
2169 0 ONLINE .003898621 .003898621 0
2168 0 ONLINE .003898621 .003898621 0
2167 0 ONLINE .003898621 .003898621 0
2166 0 ONLINE .003898621 .003898621 0
2187 0 ONLINE .008781433 .008781433 0
39 1 PENDING OFFLINE .062614441 3.99816132 220
2188 0 ONLINE 2.791008 3.978508 0
39 rows selected.
SQL> select a.usn,d.addr,e.sid,e.serial# from v$rollname a,v$rollstat b,v$transaction d,v$session e where a.usn=d.xidusn and d.addr=e.taddr and b.status='PENDING OFFLINE'
2 ;
USN ADDR SID SERIAL#
---------- ---------------- ---------- ----------
39 07000004C8DA80A0 3202 37979
SQL> alter system kill session '3202,37979';
System altered.
SQL> select a.usn,d.addr,e.sid,e.serial# from v$rollname a,v$rollstat b,v$transaction d,v$session e where a.usn=d.xidusn and d.addr=e.taddr and b.status='PENDING OFFLINE';
no rows selected
============
Undo Tablespace Moved To Pending Switch-Out State [ID 341372.1]
SQL> select ADDR, XIDUSN,STATUS, START_TIME from v$transaction;
ADDR XIDUSN STATUS START_TIME
-------- ---------- ---------------- --------------------
1477F404 5 ACTIVE 01/21/06 15:48:53
SOLUTION:
~~~~~~~~~
Run the following query, which will return the Rollback Segment Number, name of the rollback segment, status of the rollback segment, undo tablespace name, address of transaction state object, session identifier, session serial number which uniquely identifies a session's objects, username, program, machine id and operating system client user name.
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';
Ex:
USN NAME STATUS TABLESPACE_NAME ADDR SID SERIAL# USERNAME PROGRAM
--- --------- --------------- --------------- -------- ---- -------- -------- -----------
5 _SYSSMU5$ PENDING OFFLINE UNDOTBS1 1477F404 9 2976 SYS sqlplus.exe
MACHINE OS_USER
---------------------- --------------------
IDC-ORACLE\bsriniva-pc bsriniva-pc\bsriniva
ALTER SYSTEM KILL SESSION ', ';
References
NOTE:1013221.6 - RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE
