Long Running Queries

We can use view to check long running queries.

RAC / Cloud RDS:

SELECT distinct sl.SID, sl.SERIAL#,s.sql_id, sl.opname, sl.SOFAR, sl.TOTALWORK,sl.target,ROUND(sl.SOFAR/sl.TOTALWORK*100,2) COMPLETE,sq.sql_text
FROM   gV$SESSION_LONGOPS sl, gv$session s, gv$sql sq
WHERE sl.sid=s.sid and s.sql_id=sq.sql_id and
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;

NON-RAC / Cloud RDS:

SELECT distinct sl.SID, sl.SERIAL#,s.sql_id, sl.opname, sl.SOFAR, sl.TOTALWORK,sl.target,ROUND(sl.SOFAR/sl.TOTALWORK*100,2) COMPLETE,sq.sql_text
FROM   V$SESSION_LONGOPS sl, v$session s, v$sql sq
WHERE sl.sid=s.sid and s.sql_id=sq.sql_id and
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;