3.16 数据库的锁表处理

 

多集群查询锁表SQL

select b.owner,b.object_name,a.session_id,a.locked_mode

    from v$locked_object a,dba_objects b where b.object_id = a.object_id;

    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;

    alter system kill session '408,62752'; -- 杀session,对应sid serial#

 

    --查询进程

    SELECT object_name,l.OS_USER_NAME, machine, s.sid, s.serial#

    FROM gv$locked_object l, dba_objects o, gv$session s

    WHERE l.object_id　= o.object_id

    AND l.session_id = s.sid;

 

    --解决锁表

    declare cursor mycur is

    select b.sid,b.serial#

    　　from v$locked_object a,v$session b

    　　where a.session_id = b.sid group by b.sid,b.serial#;

    begin

    for cur in mycur

    loop

    execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');

 

    end loop;

    end;

 

    ---查看造成锁表的语句

 

    select sql_text from v$sql where hash_value in (

    select sql_hash_value from v$session where sid in (select session_id from v$locked_object)

    )

    --查看那个用户那个进程照成死锁

 

    select b.username,a.OS_USER_NAME,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;

 

---多个节点服务器跟本地锁表

----查询锁表

SELECT O.OBJECT_NAME,

       L.OS_USER_NAME,

       S.MACHINE,

       S.SID,

       S.SERIAL#,

       S.INST_ID,

       S.PROGRAM,

       S.TERMINAL,

       S.OSUSER,

       TO_CHAR(GS.SQL_FULLTEXT)

  FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$SQL GS

 WHERE L.OBJECT_ID　 = O.OBJECT_ID

   AND L.SESSION_ID = S.SID

   AND GS.SQL_ID(+) = S.SQL_ID;

 

ALTER SYSTEM KILL SESSION '43,36774,@2';