Identify Long running queries

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

/* To find the long running queries details */

SET LINESIZE 132

COLUMN SSID FORMAT 999999 HEADING SID
COLUMN SSERIAL FORMAT 999999 HEADING SERIAL
COLUMN OPNAME FORMAT A15 HEADING OPERATION
COLUMN TARGET FORMAT A40 HEADING TARGET
COLUMN ES FORMAT 9999999.90 HEADING "TIME RAN|(in Min's)"
COLUMN TR FORMAT 9999999.90 HEADING "TIME LEFT|(in Min's)"
COLUMN PCT FORMAT 990 HEADING "PCT"
COLUMN RATE FORMAT A10 HEADING "I/O RATE|per Min" JUST RIGHT

SELECT SID SSID, SERIAL# SSERIAL,
       SUBSTR(OPNAME,1,15) OPNAME,
       TARGET,
       TRUNC((SOFAR/TOTALWORK)*100) PCT,
       TO_CHAR(60*SOFAR*8192/(24*60*(LAST_UPDATE_TIME - START_TIME))/1024/1024/60, '9999.0')||'M' RATE,
       ELAPSED_SECONDS/60 ES,
       TIME_REMAINING/60 TR
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING > 0
ORDER BY START_TIME
/