SELECT SS.USERNAME,
SS.SID,
SS.SERIAL#,
SS.MACHINE,
SS.PROGRAM,
SL.OPNAME,
SL.TARGET,
SL.START_TIME,
ROUND(SL.SOFAR * 100 / SL.TOTALWORK, 0) || '%' AS PROGRESS,
SL.TIME_REMAINING,
SL.ELAPSED_SECONDS,
S.SQL_ID,
S.SQL_TEXT
FROM V$SESSION SS, V$SESSION_LONGOPS SL, V$SQL S
WHERE SL.SID = SS.SID
AND SS.SQL_ADDRESS = S.ADDRESS
AND SS.SQL_HASH_VALUE = S.HASH_VALUE
AND SL.TIME_REMAINING <> 0;
---或者
SELECT SE.SID,
OPNAME, ---简要说明
-- TARGET,---操作对象
-- TARGET_DESC,--目标对象说明
START_TIME, ---开始操作时间
LAST_UPDATE_TIME,
TIME_REMAINING, ---预计完成剩余秒
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK, --SOFAR 迄今为止完成的工作量，TOTALWORK 总工作量
ELAPSED_SECONDS ELAPSED, ---从操作开始总花费时间(秒)
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;