查询数据库等待事物与锁的处理

王亮

查询行锁
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY,c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;

查询数据库等待会话：
select  event,count(*) from v$session_wait group by event order by 2 desc;

查询blocking锁
select sid from gV$session where blocking_session is not null;

查询被锁的用户
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id=b.sid order by b.logon_time;

kill掉锁住的SID
alter system kill session 'SID,SERIAL#';

查询最近日志应用
select thread#,max(sequence#) "Last Standby Seq Applied"
  from v$archived_log val,v$database vdb
where val.resetlogs_change#=vdb.resetlogs_change#
    and val.applied='YES' group by thread# order by 1;