In the current times have no sense transactions of more than 24 hours (This limit is based on my experience) unless perform operations like Cross Join, FTS order of hundreds of millions (It must perform commit each n times or you run as of Undo), you uses hardware obsolete or older versions of Oracle.
It are usually small transactions (OLTP type) in which the user need to do commit or rollback (push button ok or cancel button) to end transaction. Maybe coffee time, has gone home or on vacation. And that eventually they may cause locks. Depending on the size of our installation (especially in large environment, the order of thousands of users) and the nature of the data it using the user/process. For surely we’ll find some lock occurs.
Therefore it is desirable to have a process that daily check to notify us of such transactions. An example could be the query below.
SELECT SID, USERNAME, OSUSER, PROGRAM, LOGON_TIME, LAST_CALL_ET FROM V$SESSION WHERE TADDR IS NOT NULL AND LAST_CALL_ET >= 86400 AND -- 24 Hours TYPE != 'BACKGROUND' -- != Is not ISO/ANSI SQL /
The same could be run from AT, CRON or GRID.
HTH – Antonio NAVARRO