oracle 长连接、锁查询

锁

SELECT *
  FROM (SELECT N.SID SID_,
               N.USERNAME 实例名,
               N.LOGON_TIME 登录打开页面时间,
               N.SQL_EXEC_START 当前语句开始执行时间,
               N.SQL_ID 当前SQLID,
               L.SQL_TEXT 当前SQL内容,
               L.SQL_FULLTEXT 当前SQL完整内容,
               N.EVENT 当前等待事件,
               N.FINAL_BLOCKING_SESSION 锁源头,
               N.MACHINE 机器名,
               N.INST_ID 节点,
               N.*,
               ROW_NUMBER() OVER(PARTITION BY N.SID ORDER BY N.LOGON_TIME, N.PREV_EXEC_START) RN
          FROM GV$SESSION N
          JOIN GV$SQL L
            ON L.SQL_ID = N.SQL_ID
         WHERE N.STATUS = 'ACTIVE'
           AND N.SCHEMANAME <> 'SYS'
         ORDER BY N.LOGON_TIME, N.PREV_EXEC_START) T
 WHERE T.RN = 1
 ORDER BY T.SQL_EXEC_START;

 

---长事务
set linesize 200
set pagesize 5000
col transaction_duration format a45
 
with transaction_details as
( select inst_id
  , ses_addr
  , sysdate - start_date as diff
  from gv$transaction
)
select s.username
, to_char(trunc(t.diff))
             || ' days, '
             || to_char(trunc(mod(t.diff * 24,24)))
             || ' hours, '
             || to_char(trunc(mod(t.diff * 24 * 60,24)))
             || ' minutes, '
             || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
             || ' seconds' as transaction_duration
, s.program
, s.terminal
, s.status
, s.sid
, s.serial#
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc

 

--长连接
select distinct g.INST_ID,
machine,
sid,
g.SERIAL#,
event,
g.sql_id,
(last_call_et) / 60,
v.SQL_TEXT,
g.status
from gv$session g
join gv$sqlarea v
on g.SQL_ID = v.SQL_ID
where status in ( 'ACTIVE', 'INACTIVE')
and username != 'SYS'

 

 select * from gv$session where sid=4720;