oracle数据库发生锁表现象,导致应用访问异常
处理方法
1.执行以下sql语句,查询出所有阻塞的进程:
SELECT distinct l.session_id sid, -- 持有锁的会话SID
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
s.SQL_EXEC_START,
a.sql_text,
a.action,
s.STATUS
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#
2.将以上sql结果中sid、serial#,执行杀掉对应的会话进程语句:
alter system kill session ‘’’ || SID || ‘,’ || SERIAL# || ‘’’;
例:
alter system kill session '19,5884';
alter system kill session '91,51520';
3.可以直接执行以下sql,批量查询出需要执行的语句:
SELECT 'alter system kill session ''' || SID || ',' || SERIAL# || ''';'
FROM (SELECT distinct l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
s.SQL_EXEC_START,
a.sql_text,
a.action,
s.STATUS
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#
)