可以用Spotlight软件对数据库的运行状态进行监控。 当出现session锁时，我们要及时进行处理. 1. 查看哪些session锁: SQL语句：select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1); SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1); 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';' -------------------------------------------------------------------------------- alter system kill session '132,731'; alter system kill session '275,15205'; alter system kill session '308,206'; alter system kill session '407,3510'; 2. 查看session锁. sql语句：select s.sid, q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid = &sid order by piece; SQL> select s.sid,q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid in (select sid from v$lock where block = 1) order by piece; SID SQL_TEXT ---------- ---------------------------------------------------------------- 77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED 77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON 77 E=9 WHERE PROFILE_USER.ID=:34 3 rows selected. 3. kill锁的进程. SQL语句：alter system kill session '77,22198'; SQL> alter system kill session '391,48398'; System altered. 4. 查看谁锁了谁。select s1.username || [email='@']'@'[/email] || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || [email='@']'@'[/email] || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;注： > ： 重定向输出，将文件的标准输出重新定向输出到文件，或将数据文件作为另一程序的标准输入内容。 | ：UNIX管道：将一文件的输出作为另一文件的输入. 在执行SQL语句试：alter system kill session '391,48398'(sid为391); 应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill.
------------------------------------------------------csdn 网友的SQL-------------------------------
SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE, DECODE (m.lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', lmode, LTRIM (TO_CHAR (lmode, '990')) ) lmode, DECODE (m.request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', request, LTRIM (TO_CHAR (m.request, '990')) ) request, m.id1, m.id2 FROM v$session sn, v$lock m WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞 OR ( sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定 AND m.request = 0 AND lmode != 4 AND (id1, id2) IN ( SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2) )ORDER BY id1, id2, m.request;alter system kill session 'sid,SERIAL#';
转载于:https://www.cnblogs.com/hibernate315/archive/2009/10/27/2399385.html
