Long Running Operation in Oracle

There is a dynamic performance view v$session_longops that is populated for many long running operations in Oracle. The primary criterion for any operation to appear in v$session_longops is to run more than 6 seconds. Although this isn’t the only criterion as well as not all operations that take more than 6 seconds are shown in this view. For example one can find hash joins in v$session_longops, but you won’t find there nested loop joins even if they are longer than 6 seconds and are joining very big data sets.

select opname, target, sofar, totalwork, units, elapsed_seconds, messagefrom v$session_longops order by
start_time desc

Or If we want to know about a specific session.Then,

select opname, target, sofar, totalwork, units, elapsed_seconds, message from v$session_longops
where sid = and serial# = order by start_time desc

Built in types of long operations:
Each new version of Oracle adds several new types of built in long operations that are shown in v$session_longops. Some of them are:

* Table scan;
* Index Fast Full Scan;
* Hash join;
* Sort/Merge;
* Sort Output;
* Rollback;
* Gather Table's Index Statistics.

The other option is to query v$transaction and look at used_ublk (used blocks) and used_urec (used records) values. They don't show precisely the deleted amount but at least you'll know the trend and if they are increasing it means delete is not completed yet.
Again,Repeated SELECTs against V$SESS_IO can/will show I/O counts increasing.