plsql表锁被占用_解决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>