oracle查询锁参考

–获取锁的基本信息

select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

–获取sql文本

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
from v$sqlarea a,
     v$session s,
     v$locked_object l
where l.session_id = s.sid
  and s.prev_sql_addr = a.address
order by sid, s.serial#;



select distinct aa.SQL_TEXT,
                t2.OSUSER,
                t2.username,
                t2.sid,
              'alter system kill session '''|| t2.sid||','||t2.serial#||''''||';' ,                t2.logon_time,
                aa.FIRST_LOAD_TIME 操作时间,
                t2.SQL_ID,
                t2.PROCESS,
                t2.MACHINE,
                aa.MODULE,
                aa.ACTION,
                ab.EVENT 事件
  from v$locked_object t1, v$session t2, v$sql aa,v$session_wait ab
 where t1.session_id = t2.sid
   and t2.SQL_ID = aa.SQL_ID
   and t2.SID=ab.SID
 order by t2.logon_time;

当session是active的时候，alter system kill session 只是将session标识为killed
或者pseudo状态，并不会释放session持有的资源，所以我们在执行完alter system kill session 后，看会话还
是一直存在。
这种情况下可以使用 immediate选项，强制立即Kill会话，如下：
alter system kill session '3964,51752' immediate；

–获取未提交sql, 没有提交的sql不一定查询的到，有可能从共享池里面被移除了

select s.sid
     , s.serial#
     , s.username
     , s.osuser
     , s.program
     , s.event
     , to_char(s.logon_time, 'yyyy-mm-dd hh24:mi:ss')
     , to_char(t.start_date, 'yyyy-mm-dd hh24:mi:ss')
     , s.last_call_et
     , s.blocking_session
     , s.status
     , (
    select q.sql_text
    from v$sql q
    where q.last_active_time = t.start_date
      and rownum <= 1) as sql_text
from v$session s,
     v$transaction t
where s.saddr = t.ses_addr;

–查询package被哪些session锁住, ddl锁

select distinct aa.session_id sid,
                'alter system kill session ''' || aa.session_id || ',' ||
                vv.serial# || '''' || ';',
                aa.session_id sid,
                owner,
                name,
                aa.type,
                mode_held held,
                mode_requested request
  from dba_ddl_locks aa, v$session vv
 where aa.name = 'xxx_PUB_INSERT'
   and aa.session_id = vv.SID;
   

有不对的地方，还请指正，谢谢