Monitoring oracle long running sessions

To monitor long running operations in oracle the following two conditions must met.
1) Set the initialization parameter TIMED_STATISTICS or SQL_TRACE parameters to true.
2) Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.

After you have met above two conditions you can easily monitor your long running operations by querying V$SESSION_LONGOPS view.

SQL> desc V$SESSION_LONGOPS
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
SERIAL# NUMBER
OPNAME VARCHAR2(64)
TARGET VARCHAR2(64)
TARGET_DESC VARCHAR2(32)
SOFAR NUMBER
TOTALWORK NUMBER
UNITS VARCHAR2(32)
START_TIME DATE
LAST_UPDATE_TIME DATE
TIMESTAMP DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_PLAN_HASH_VALUE NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
SQL_PLAN_LINE_ID NUMBER
SQL_PLAN_OPERATION VARCHAR2(30)
SQL_PLAN_OPTIONS VARCHAR2(30)
QCSID NUMBER
From above view,
- SOFAR is units of work done so far.
- TIME_REMAINING is estimate (in seconds) of time remaining for the operation to complete

In order to monitor how much percentage of an operation is completed query as,

SQL> SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops;

If you have long running operations in your database then above query will return rows.

You can query long running operations based on they have started like,

SQL> select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops order by start_time desc;