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';
No comments:
Post a Comment