long running query

–Show queries in progress.
col opname for a25 trunc
col username for a15 trunc
col target for a20
col sid for 999999
col serial# for 999999
col %DONE for a8
select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100 / b.TOTALWORK,0) || ‘%’ as “%DONE”,
b.TIME_REMAINING,to_char(b.start_time,’YYYY/MM/DD HH24:MI:SS’) START_TIME
from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING 0 order by b.SOFAR/b.TOTALWORK;

–variation on sql above
col opname for a25
col progress for a15
col progress for a8
select a.sid,b.username,b.opname,round(b.SOFAR*100 / b.TOTALWORK,0) || ‘%’ as progress,
b.TIME_REMAINING,b.target from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING 0 order by 5;

–show long operations.
column c4 heading “%age Completed” format 99,99.99
column TotalWork format a15
column ELAPSED_SECONDS format 99999
select username,sofar,totalwork||units “TotalWork”,(sofar/totalwork)*100 c4,to_char(start_time,’mm-dd-yyyy hh24:mi’) “Start_Time”,
elapsed_seconds,sql_address from V$SESSION_LONGOPS where sofar/totalwork < 1 order by elapsed_seconds;

–show long operations. it shows jobs that have finished too
set linesize 150
col username for a20
col target for a35
col opname for a25 trunc
col sid for 999999
col serial# for 999999
col sofar for 9999999
select a.username,b.sid,b.serial#,b.opname,b.target,b.sofar,b.totalwork,to_char(b.start_time,'HH24:MM:SS') START_TIME,
to_char(SYSDATE,'HH24:MM:SS') CURR_TIME from V$SESSION_LONGOPS b, V$SESSION a where a.sid=b.sid order by 8,9;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: