I have a time consuming query which populates a materialized view. It has been running for 18 hours – which was not expected.
SELECT STATUS
FROM V$SESSION
WHERE SID=194;
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.
UPDATE
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.
Best Answer
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"