PLSQL_Oracle Table Lock表级锁的处理(案例)

20150506 Created By BaoXinjian

一、摘要


当某个数据库用户在数据库中插入、更新、删除一个表的数据,或者增加一个表的主键时或者表的索引时,常常会出现ora-00054:resource busy and acquire with nowait specified这样的错误。

主要是因为有事务正在执行(或者事务已经被锁),所有导致执行不成功。

 

二、模拟表级锁产生


Step1. 在一个session中对表bxj_regions做更新操作

Step2. 在另外一个session对该表进行Truncate操作后,该表因为被锁住,会产生相应Error

 

三、处理表级锁过程


Step1. 查看数据库中哪些对象处在被锁状态 

select t2.username, t2.sid, t2.serial#, t2.logon_time, t3.owner, t3.object_name, t3.object_type
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
 order by t2.logon_time;

Step2. 根据sid, 查看该session所运行的sql语句 

select a.username, a.sid, a.serial#, b.sql_text
  from gv$session a, gv$sqltext b
 where a.sql_address = b.address
   and a.sid = 23
   and a.inst_id = 1
 order by b.piece

Step3. 根据sid,查看该session是由哪个用户发起的 

select a.username,
       a.inst_id,
       a.sid,
       a.serial#,
       b.spid "OS Process",
       to_char(a.logon_time,'DD/MM/YYYY hh24:mi:ss'),
       a.command,
       a.osuser,
       a.program,
       a.module,
       a.status
from gv$session a, gv$process b
where a.inst_id = 1
and a.sid = 23
and a.paddr = b.addr
and a.inst_id = b.inst_id

Step4. 如果确认该session可以进行删除, 则kill session, 若时间操作过久,对象被锁住太长

alter system disconnect '23,45832'  immediate;

 

Thanks and Regards


posted on 2014-06-22 22:33  东方瀚海  阅读(1399)  评论(0编辑  收藏  举报