可以通过v$session 的 last_call_et 这个字段 来获得你所需要的信息:
last_call_et 代表某个session 自上次调用以来的持续时间,单位是s ,可以和status 结合起来使用.
以下是个小例子.
1. 一个session 执行如下匿名过程,执行时间180s.
SQL> select distinct sid from v$mystat;
SID
----------
39
SQL>
SQL> declare
2 begin
3 dbms_lock.sleep(180);
4 end;
5 /
PL/SQL procedure successfully completed
SQL>
2. 另一个session 来查看 39 号session 的last_call_et 信息.
SQL> set time on
16:01:59 SQL> set timing on
16:02:02 SQL> select sid,username ,last_call_et from v$session where sid=39;
SID USERNAME LAST_CALL_ET
---------- ------------------------------ ------------
39 APPS 66
Executed in 0.031 seconds
16:02:21 SQL> /
SID USERNAME LAST_CALL_ET
---------- ------------------------------ ------------
39 APPS 69
Executed in 0.032 seconds
16:02:31 SQL> select sid,username ,status,last_call_et from v$session where sid=39;
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 93
Executed in 0.031 seconds
16:02:46 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 96
Executed in 0.031 seconds
16:02:48 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 96
Executed in 0.031 seconds
................................
16:03:06 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 117
Executed in 0.031 seconds
16:03:11 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 120
Executed in 0.047 seconds
16:03:14 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 126
Executed in 0.047 seconds
16:03:18 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 129
Executed in 0.031 seconds
16:03:21 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 129
Executed in 0.031 seconds
16:03:22 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 129
Executed in 0.047 seconds
16:03:24 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 147
Executed in 0.031 seconds
16:03:42 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS ACTIVE 168
Executed in 0.062 seconds
16:04:02 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS INACTIVE 15
Executed in 0.078 seconds
16:04:28 SQL> /
SID USERNAME STATUS LAST_CALL_ET
---------- ------------------------------ -------- ------------
39 APPS INACTIVE 18
Executed in 0.062 seconds
16:04:32 SQL>