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
ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建