oracle查询会话时长,【oracle】会话查询

1，查询当前会话

SELECT S.SCHEMANAME

,S.MACHINE AS CLIENT_MACHINE_NAME

,S.OSUSER AS CLIENT_OSUSER

,S.PROGRAM AS CLIENT_PROGRAM

,S.SID

,S.SERIAL#

,S.STATUS

,TO_CHAR(S.LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LOGON_TIME

,S.LAST_CALL_ET

,S.SQL_ADDRESS

,S.SQL_HASH_VALUE

,S.PREV_SQL_ADDR

,S.PREV_HASH_VALUE

FROM V$SESSION S

ORDER BY S.SCHEMANAME

,S.MACHINE

,S.OSUSER

,S.PROGRAM

,S.SID;

2，通过SID和SERIAL#可以定位唯一的一个会话，所以删除语句如下

ALTER SYSTEM KILL SESSION ','

其中和为上面语句的查询结果

3，有时候我们想知道某个会话锁住了哪些表，可以通过查询V$LOCKED_OBJECT来完成。

SELECT S.SCHEMANAME

,S.MACHINE AS CLIENT_MACHINE_NAME

,S.OSUSER AS CLIENT_OSUSER

,S.PROGRAM AS CLIENT_PROGRAM

,S.SID

,S.SERIAL#

,S.STATUS

,TO_CHAR(S.LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LOGON_TIME

,S.LAST_CALL_ET

,LK.ORACLE_USERNAME

,(SELECT OBJECT_NAME

FROM DBA_OBJECTS

WHERE OBJECT_ID = LK.OBJECT_ID) OBJECT_NAME

,LK.OS_USER_NAME

,LK.LOCKED_MODE

FROM V$SESSION S

JOIN V$LOCKED_OBJECT LK

ON LK.SESSION_ID = S.SID

ORDER BY S.SCHEMANAME

,S.MACHINE

,S.OSUSER

,S.PROGRAM

,S.SID;

4，指定会话执行的SQL语句可以通过查询V$SQLTEXT来完成

因为只保存当前和上一次执行的SQL地址，所以这个查询对于开发调查的实际意义并不大