I have a time consuming query which populates a materialized view. It has been running for 18 hours - which was not expected.


The above query shows the session as 'ACTIVE'. I was wondering if that is sufficient proof that the query is still running and whether there is any other way to double check that the session is not dead.


The session was not dead; but there was a constant wait:

 SQL*Net more data from dblink 

I used sql trace as suggested by Phil to reach this conclusion.

  • 1
    What does v$session_longops say? you can use sofar/totalwork to see what percentage done it is. If that draws a blank, trace the session and see if anything appears in the trace file
    – Philᵀᴹ
    Mar 18, 2015 at 10:59
  • 'v$session_longops' gives blank. Thanks for the tip; I will have a look into tracing the session.
    – Stelios
    Mar 18, 2015 at 11:00
  • I will have to rerun the query to run the trace on it; which is a bit inconvenient in this case.
    – Stelios
    Mar 18, 2015 at 12:33

1 Answer 1


You should query V$SESSION_WAIT to see what your statement is doing. A short description of the wait events can be found in "Part IV Appendixes", "C Oracle Wait Events" of the "Database Reference"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.