Long-running sessions in Oracle

 Long-running sessions in Oracle using below queries:

You can check long running sessions using v$session_longops script which will show you, % completed, remaining time, sofar completed and much more detailed information. You can also use below queries to kill long running sessions in Oracle.

 

Query1:

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;

Query2:

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;

 

 

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='bgf07y9xn8grx';



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';