Finding session using UNDO tablespace


The below query can be used to find the session which is consuming more UNDO tablespace.

Query:

SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc;


OR

set pages 1000
set lines 1000
set trims on
col OSPID for a10
col TABLESPACE_NAME for a15
col USERNAME for a15
col MACHINE for a15
col TERMINAL for a15

select
  p.spid OSPID, d.TABLESPACE_NAME, t.STATUS, t.START_TIME, s.SID, s.SERIAL#, s.USERNAME,
  s.SQL_EXEC_START, s.MACHINE, s.TERMINAL, s.SQL_ID, s.STATE, s.PROGRAM
from v$session s, v$transaction t, dba_data_files d, v$process p
where
 s.SADDR = t.SES_ADDR and
 s.paddr = p.addr and
 t.UBAFIL=d.FILE_ID and
 d.TABLESPACE_NAME='<OLD_UNDO_TABLESPACE_NAME>'
;