If you suspect that there are ACTIVE transactions that are not completing for some reason (maybe hung), you can query this fact!
This query gives us information on all ACTIVE Transactions:
select t.start_time, s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,s.type, to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr and s. status = 'ACTIVE'
order by start_time
Remember – mere presence of a ACTIVE transaction here does not mean it is long-running. It only means it is running ‘right-now’ You must look at t.start_time column and compare it with current sysdate/time to find running time of the transaction.
You can remove the s.status=’ACTIVE’ filter to see INACTIVE (completed) transactions too. INACTIVE transaction records are removed after a while.
If you get errors like table not found etc. you probably do not have permission on those tables, either get those permissions or approach Oracle DBA to execute this query.