Long Running Sessions in Oracle

Long Running Sessions in Oracle

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

(or)

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING; 

Above output you can further check the sql_id, sql_text and the wait event for which query is waiting

TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';

To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='1uksqt2vzxbz5';

To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';