注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired  

2011-11-14 15:17:47|  分类: oracle问题 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

ORA-12012: error on auto execute of job 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

解决办法:

方法一:

查看该Job执行的内容是,每天准时清理,下一天的日志表,而该表是partition表,当执行alter table truncate partition p1;在一定的时间内没有执行完,而正好此时有数据写入表的时候,就会出现这样的情况,可以通过DDL_LOCK_TIMEOUT的设置来解决,下面是对该参数的说明:

 

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1

DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the DDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

The 
DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don't commit the insert.

CREATE TABLE lock_tab (

id NUMBER

);

 

INSERT INTO lock_tab VALUES (1);

Leave this session alone and in a new session, set the DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to add a column to the table.

ALTER SESSION SET ddl_lock_timeout=30;

 

ALTER TABLE lock_tab ADD (

description VARCHAR2(50)

);

The session will wait for 30 seconds before failing.

ALTER TABLE lock_tab ADD (

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If we repeat the ALTER TABLE command and commit the insert in the first session within 30 seconds, the ALTER TABLE will return a successful message.

ALTER TABLE lock_tab ADD (

description VARCHAR2(50)

);

 

Table altered.

 

SQL>

For more information see:
从上面的情况来看,当设置该参数非0的时候,就可以限制这个等待的时间

方法二:查找占用的进程并Kill会话

V$lock里面找到锁所属于的session id
select sid from v$lock where id1 = (select object_id from user_objects where object_Name='
表名') and request = 0; 

v$session里面查找相应session的所有者或者process,联系所有者或者kill process
select * from v$session where sid =
刚才的sid;

  评论这张
 
阅读(1688)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017