SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
SET TERMOUT ON
SET LINES 300
COL "CURRENTLY EXECUTING" FORMAT A35
COL OSUSER FORMAT A10
COL MACHINE FORMAT A20
COL USERNAME FORMAT A20
COL PROGRAM FORMAT A40
COL UNITS FORMAT A20
COL OPNAME FORMAT A10
COL START_TIME FORMAT A20
COL LAST_UPDATE_TIME FORMAT A20
COL SQL_ID FORMAT A15
SET VERIFY OFF
SET FEEDBACK OFF
SELECT
S.SID,
S.SERIAL#,
S.OSUSER,
S.MACHINE,
S.USERNAME,
S.PROGRAM,
S.SQL_ID,
SUBSTR(Q.SQL_TEXT,1,30) || '...' "CURRENTLY EXECUTING",
L.OPNAME,
L.SOFAR,
L.TOTALWORK,
L.UNITS,
L.START_TIME,
L.LAST_UPDATE_TIME,
L.TIME_REMAINING "SECONDS LEFT",
TO_TIMESTAMP(L.LAST_UPDATE_TIME,'DD.MM.YYYY HH24:MI:SS')-TO_TIMESTAMP(L.START_TIME,'DD.MM.YYYY HH24:MI:SS') "RUNNING FOR"
FROM V$SESSION_LONGOPS L JOIN V$SESSION S ON L.SID = S.SID
JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID
AND S.SID = &SID
AND S.SERIAL# = L.SERIAL#
ORDER BY L.LAST_UPDATE_TIME DESC;
EXIT
Result:
| SID | SERIAL# | OSUSER | MACHINE | USERNAME | PROGRAM | SQL_ID | CURRENTLY EXECUTING | OPNAME | SOFAR | TOTALWORK | UNITS | START_TIME | LAST_UPDATE_TIME | SECONDS LEFT | RUNNING FOR |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 408 | 2624 | oracle | testserver1 | SH | sqlplus@testserver1 (TNS V1-V3) | 3w11rcbvd5a32 | UPDATE SALES_Q1_DI_MASTERS SET ... | Table Scan | 82432 | 82636 | Blocks | 06.03.2014 07:35:26 | 06.03.2014 09:44:22 | 19 | +00 02:08:56.000000 |
No comments:
Post a Comment