check Undo users and uncommited transactions in Oracle

set lines 300
set pages 30000
SELECT a.sid, a.username, b.xidusn, b.used_urec Num_of_records, b.used_ublk USED_BLOCKS,(b.used_ublk * ( select value from v$parameter where name = 'db_block_size'))/1024/1024 MB_USED
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;


select
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) TOTAL_UNDO_SIZE_MB,
nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) TOTAL_UNDO_USED_MB,
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) - nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) FREE_MB_UNDO
from dual;


--#uncommited transactions

col osuser for a10
col username for a10
set lines 300
set pages 300
col sql_text for a60


select V$SESSION.OSUSER,spid,V$SESSION.status,
V$SESSION.USERNAME,V$SESSION.SID,
V$SESSION.SERIAL#,
V$SESSION.PROGRAM,
V$SESSION.MACHINE,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from
V$SESSION , v$transaction,V$process
WHERE v$transaction.ses_addr = V$SESSION.saddr and V$process.addr = V$SESSION.paddr;


select OSUSER,V$SESSION.status,
USERNAME,SID,
SERIAL#,
SQL_TEXT,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from v$transaction ,
V$SESSION ,
V$SQLAREA
WHERE( (V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS and V$SESSION.sql_hash_value = V$SQLAREA.HASH_VALUE )
OR ( V$SESSION.PREV_SQL_ADDR = V$SQLAREA.ADDRESS and V$SESSION.PREV_hash_value = V$SQLAREA.HASH_VALUE ))
and v$transaction.ses_addr = V$SESSION.saddr;