OEM will show it too, under session you will see a little alarm clock thingymabob icon, but it is not very reliable.
I have noticed in OEM that some sessions will show "inactive" when in fact they are active, maybe they like to take naps?
rshivagami, V$SESSION_LONGOPS do NOT works regarding "execution time" for long SQLs, it works timing long OPERATIONS for the SQLs : this way, IF a given SQL is doing a lot of SMALL operations , you WILL NOT "see" this in v$session_longops, the views shows ONLY if the SQL is doing JUST ONE large operation, such a large full table scan, thatīs it. And more, tipically int the same SQLs many times the bd spend time doing some "large" steps (like FTSs) AND doing small operations (like indexed-key reads and the like), V$SESSION_LONGOPS show only the large ones.
It will not show the time spent, of course, BUT if one checks and see query X being executed, 2 minutes later see again and again X is there, and another 2 minutes X stiil is there, sure X is a query "slow" , something is wrong with X, this is what one can do to identify "long-running" queries ....