oracle之 RA-00054: resource busy and acquire with NOWAIT specified or timeout expired

1、 truncate 表报 ORA-00054 ,标明有事务正在操作该表

SQL> truncate table alldm.DM_XQKD_YUJING_D;

truncate table alldm.DM_XQKD_YUJING_D

*

ERROR at line 1:

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

2、 找到正在操作该表的session与sql

SQL> set linesize 400

SQL> set pagesize 400

SQL> col object_name for a40

SQL> select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects where object_name=’DM_XQKD_YUJING_D’;

OWNER OBJECT_NAME OBJECT_ID

—————————— —————————————- ———-

ALLDM DM_XQKD_YUJING_D 7525915

SQL> select SESSION_ID,OBJECT_ID from v$locked_object where OBJECT_ID = ‘7525915’;

SESSION_ID OBJECT_ID

———- ———-

2226 7525915

SQL> set linesize 400

SQL> set pagesize 400

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: 2226

old 3: and a.sid=&sid order by piece

new 3: and a.sid=2226 order by piece

SQL_TEXT

—————————————————————-

INSERT /*+APPEND*/ INTO DM_XQKD_YUJING_D SELECT /*+ORDERED*/ :B2

, A.AREA_NO, A.CITY_NO, A.XIAOQU_NO PLOT_ID, A.XIAOQU_NAME PLOT

3、 找到该session的os进程

select a.username,

a.sid,

a.serial#,

b.spid “OS Process”,

to_char(a.logon_time,’DD/MM/YYYY hh24:mi:ss’) “Logon time”,

a.osuser,

a.program,

a.status

from v$session a, v$process b

where a.sid = &sid

and a.paddr = b.addr

/

USERNAME SID SERIAL# OS Process Logon time OSUSER PROGRAM STATUS

—————————— ———- ———- ———————— ——————- —————————— ———————————————— ——–

ALLDM 2226 28311 76949 13/01/2018 06:04:24 bca JDBC Thin Client ACTIVE

4、 kill session

alter system kill session ‘2226,28311’;

5、 在os层面核实进程是否被kill

ps -ef | grep 76949

6、 再次truncate,truncate成功。

博客园精华区稿源:博客园精华区 (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » oracle之 RA-00054: resource busy and acquire with NOWAIT specified or timeout expired

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录