oracle 锁问题的解决

可以用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
&nb