SQL-Get Long Running Query Status In Sql*Plus

If you are using tools like TOAD to track a session, in SESSION BROWSER it has operations labeled as "longops". When u would click on the long operation, it would give you a status/percentage complete bar. This bar tells you how many blocks it needs to process for a given operation such as an index scan, and how many blocks it's completed so far.

But if you are using tool like SQL*PLUS it is not possible, if u want view the same result u can use the below query:

select sid ,start_time, time_remaining,(sofar/totalwork)*100 percent ,elapsed_seconds,message from v$session_longops

where sid=<your session id>;

EX:- select sid ,start_time,time_remaining,(sofar/totalwork)*100 percent  , elapsed_seconds,message from v$session_longops where sid=556;

　

How do you get active session id?

select * from v$session where username=<username> and status='ACTIVE';

EX:- select * from v$session where username='SCOTT' and status='ACTIVE';

Get sid from the above query and run below query using that sid, it gives you exact seesion id on which your process is running. 

SELECT  c.sql_text FROM v$process a, v$session b, v$sqltext c

WHERE B.SID=<your sid> AND a.addr=b.paddr AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE' ORDER BY a.spid, c.piece

SELECT  c.sql_text FROM v$process a, v$session b, v$sqltext c

WHERE B.SID=556 AND a.addr=b.paddr AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE' ORDER BY a.spid, c.piece