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