Oracle defines long ops as operations that run for longer
than six seconds in absolute time, including some backup and recovery
functions, statistics gathering, and query execution.
COLUMN percent FORMAT 999.99
SELECT sid,
to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100
percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
OR
|
select s.osuser||'@'||s.machine||'$>'||program who,
|
|
sql.sql_text what,
|
|
lpad(
|
|
to_char(trunc(24*(sysdate-s.logon_time))) ||
|
|
to_char(trunc(sysdate) + (sysdate-s.logon_time),
':MI:SS')
|
|
, 10, ' ') AS UP_time,
|
|
lops.sofar/lops.totalwork percentage,--gives divide by
0 exception
|
|
lops.time_remaining,
|
|
nvl(lops.opname||':'||lops.message,'no long operations')
operation
|
|
from gv$session s
|
|
join gv$sql sql on sql.sql_id = s.sql_id
|
|
join gv$session_longops lops on lops.sid = s.sid
|
|
and lops.serial# = s.serial#
|
|
where s.type!='BACKGROUND'
|
|
and s.status='ACTIVE' and s.sql_id is not null
|
If you are using TOAD
Click on the Sessions tab, if it has not been selected.
Select one or more sessions from the sessions tree view in the top panel.
In the bottom panel, click the Long Ops tab.
Note: You may have to scroll through the tabs in the bottom panel to see the Long Ops tab.
Percent Column Calculation
The Percent column is calculated as:
decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2))
Select one or more sessions from the sessions tree view in the top panel.
In the bottom panel, click the Long Ops tab.
Note: You may have to scroll through the tabs in the bottom panel to see the Long Ops tab.
Percent Column Calculation
The Percent column is calculated as:
decode(totalwork, 0, 0, round(100 * sofar/totalwork, 2))
