Oracle Database / Sessions, Locks & Transactions
Assess a long-running transaction before intervention
A transaction can hold locks and generate undo for much longer than the visible user request. Collect its age and scale before planning a stop.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT s.sid, s.serial#, t.start_time, t.used_ublk, t.used_urec FROM v$transaction t JOIN v$session s ON s.taddr = t.addr ORDER BY t.used_ublk DESC;Read the result
START_TIME and undo counters indicate transaction context. Large counters do not directly predict recovery time or identify a fault.
Resolve and verify
Compare the transaction with its batch schedule and application progress. Agree whether to let it finish or arrange an approved rollback, then monitor actual progress.
Operational precautions
Rollback can take substantial time and continue after session termination. Do not promise immediate lock release.