解决Oracle锁表
###############概述： ######################
锁表是很容易发生的现象，当有多人对表进行操作时就容易发生。如下是解决锁表的步骤
###############锁表原因： ####################
由于Oracle数据库具有保持数据的一致性，所以当一个人对表进行操作，没有提交，另一个人在需要操作的时候，就不能操作，必须等表提交，就一直处于等待状态，就导致锁表。
###############锁表报错信息： ##################
如果发现修改一个表数据时不能提交而报下面的错误：
Record is locked by another user
###############解决方法： #####################
杀死会话进程或者再执行sql语句，然后提交。
###############解决步骤： ####################
1. 登陆数据库，以sys用户登陆
SQL> conn /as sysdba
Connected.
SQL>
SQL>
SQL>
2. 查询哪些会话被锁。
SQL> select t2.username, t2.sid, t2.serial#, t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
USERNAME SID SERIAL# LOGON_TIME
------------------------------ ---------- ---------- -------------------
IPMS 494 10320 2014-01-23 08:03:38
IPMS 479 25009 2014-01-23 08:04:50
HCBA 401 59957 2014-01-23 08:43:23
SQL>
SQL>
SQL>
2.1. 查看会话具体内容
SQL> select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=&sid order by piece;
Enter value for sid: 494 ------ (在此输入sid号)
old 1: select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=&sid order by piece
new 1: select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=494 order by piece
SQL_TEXT
----------------------------------------------------------------
UPDATE IPMS.IS_STANDARD D SET D.IS_LOCKCOUNT = D.IS_LOCKCOUNT -
(SELECT SUM(M.IM_APPLYNUM)
FROM IS_MATERIALAPPLY M
WHERE M.IM_GBID IN
(SELECT
SQL_TEXT
----------------------------------------------------------------
G.IGB_ID
FROM IPMS.IS_GRANTB
ILL G INNER JOIN IPMS.WWF_INSTANCE I ON G.IGB_FLOWID = I.INSTA
NCE_ID
WHERE I.INSTANCE_ID=:I
NSTANCE_ID)
AND D.IM_ID = M.IM_ID
AND D.IS_LOCK = 1
SQL_TEXT
----------------------------------------------------------------
)
10 rows selected.
2.2 查询会话具体内容
SQL> select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=&sid order by piece;
Enter value for sid: 479(在此输入sid号)
old 1: select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=&sid order by piece
new 1: select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=479 order by piece
SQL_TEXT
----------------------------------------------------------------
begin
sys.dbms_output.get_line(line => :line, status => :statu
s);
end;
3. 杀死该会话
根据刚才查询出来的sid号和serial值。
SQL>
SQL> alter system kill session '494,10320';
System altered.
SQL> alter system kill session '479,25009';
System altered.
SQL> commit;
Commit complete.
SQL>