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