undo tablespace usage oracle 11g

1. UNDO tablespace is setup by below parameter
 show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

the setting means database user auto unod management and retension is 900 seconds and default undo tablespace name UNDOTBS

2. active session's undo usage

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id

3. undo block usage

select count(*), status
 from dba_undo_extents
 group by status
 /

4. swith undo tablespace

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

reference:
http://oracledisect.blogspot.com/2008/05/who-is-using-your-undo-space.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo005.htm
http://oraclezone.wordpress.com/2007/12/08/how-much-undo-does-a-sql-use/