v$rollstat 에서 상태가 "PENDING OFFLINE" 가 존재할 때

 

즐거운 샤핑 http://www.shapping.co.kr

 

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 USN, STATUS
from v$rollstat
where STATUS = 'PENDING OFFLINE';

 

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';

 

 

Compare the XIDUSN and ADDR returned by the v$transaction with the USN and ADDR returned by the above query. You will find the corresponding rollback segment details used for the transaction which is still active.

 

From the above query we can get the program, user and the machine details, so we can contact the specific users who have the rollback segments locked, to either commit or rollback the transaction. Once the transaction is commited or rolled back the alert log will no more be flooded with the errors.

 

The other option would be to kill the session that is holding the lock over the rollback segment. The above query also returns the sid# and the serial #. We can kill the session using the following statement:

 

SQL> ALTER SYSTEM KILL SESSION '<sid#>, <serial#>';