欢迎光临
我们一直在努力

oracle之 RA-00054: resource busy and acquire with NOWAIT

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 hh34: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
SQL> alter system kill session ‘2226,28311’;
System altered.

5、 在os层面核实进程是否被kill
oracle@hbdw1:/oratmp$ps -ef | grep 76949
oracle 11057 116412 0 16:21 pts/2 00:00:00 grep 76949

6、 再次truncate成功
SQL> truncate table alldm.DM_XQKD_YUJING_D;
Table truncated.

赞(0)
【声明】:本博客不参与任何交易,也非中介,仅记录个人感兴趣的主机测评结果和优惠活动,内容均不作直接、间接、法定、约定的保证。访问本博客请务必遵守有关互联网的相关法律、规定与规则。一旦您访问本博客,即表示您已经知晓并接受了此声明通告。