Monitor long operations
                                                 Last update (2009-05-13 13:55:27)
                                                                                                                   Date added (2008-02-13 19:36:34)

Summary
This view (v$session_longops) displays the status of various operations that run for longer than 6 seconds. These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.

Column explanation
LOGON: It is formatted on Days + 01:20:45 (1 hour, 20 mins and 45 secs). I like to have a feeling with a glimpse of how much time this session is running.
SID: ThatĘs all about, sessions have the session id.
USERNAME, STATUS, OSUSER, MACHINE, MODULE: Are self explanatory.
MESSAGE: What is doing, for example a full table scan
PERC: for example 34% of the total operation
REMAINING and ELAPSED: time in Days + 00:00:00 format
SELECT DISTINCT a.* FROM (SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, 
TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, status, v$session.USERNAME, v$session.osuser, 
v$session.machine, v$session.module,
MESSAGE, ROUND((SOFAR/TOTALWORK)*100,2) || '%' PERC,
DECODE(TRUNC(time_remaining/86400), 0, TO_CHAR(TO_DATE(time_remaining, 'SSSSS'), 'HH24:MI:SS'), 
TRUNC(time_remaining/86400) || ' Days + '
|| TO_CHAR(TO_DATE(time_remaining - (TRUNC(time_remaining/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) REMAINING,
DECODE(TRUNC(ELAPSED_SECONDS/86400), 0, TO_CHAR(TO_DATE(ELAPSED_SECONDS, 'SSSSS'), 'HH24:MI:SS'), 
TRUNC(ELAPSED_SECONDS/86400) || ' Days + '
|| TO_CHAR(TO_DATE(ELAPSED_SECONDS - (TRUNC(ELAPSED_SECONDS/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) 
ELAPSED, sql_text FROM v$sql, 
(SELECT * FROM v$session_longops WHERE TIME_REMAINING > 0) v$session_longops , v$session
WHERE v$session.SID = v$session_longops.SID
AND v$session_longops.sql_address = v$sql.address (+)
AND v$session_longops.sql_hash_value = v$sql.hash_value (+)
--and v$session.sid = 1644
--and osuser = 'kaparelis'
--order by address, hash_value, child_number
) a;

    
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search