ORA-00054,ORA-00031 resource busy and acquire with NOWAIT 资源占用处理过程

truncate占用资源解决办法ITPUB个人空间o#V?&ka4z
SQL> truncate table test_source;
6wS}z&KHc3G0truncate table test_source
L~
]k1N3@0               *ITPUB个人空间7B HM:|,g*]s+C!ADO/{
ERROR at line 1:ITPUB个人空间6}ctP"~Y0~Z_g
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> select object_name,session_id,oracle_username from v$locked_object t1, user_objects t2 where t1.object_id = t2.object_id;

OBJECT_NAME
w.ta9P9~hmZ(I2q0--------------------------------------------------------------------------------

SESSION_ID ORACLE_USERNAMEITPUB个人空间7VW5QI;iG$c-f
---------- ------------------------------
1b o Gq4]/Nh"I0TEST_SOURCE
mj'l%G xqJ0       190 **********

 

SQL> select sid, serial#, username, osuser from v$session where sid='190';

       SID    SERIAL# USERNAME
r8_N7IR9xy\e0---------- ---------- ------------------------------ITPUB个人空间$w(O;` J3B pKa
OSUSER
&XP5vR6EboBj0------------------------------
bi;eA,jw0       190       2028 **********ITPUB个人空间3mrS$Y:Ys.e*Xj
KYS\kyslogin

,LE j&\g}9B/F0SQL> conn sys/oracle as sysdba
zo0CX2hF!k)}1F0Connected.
9N.E\4UJaR
y0SQL> alter system kill session '190,2028';
6K"y/mehdAEc0alter system kill session '190,2028'ITPUB个人空间&f`'} [\NMI0s
*ITPUB个人空间(j `cP v,Y
ERROR at line 1:ITPUB个人空间h~Cq6f \*Au
u
ORA-00031: session marked for kill

 

SQL> select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=190;

SPID                     OSUSER
G7j;C#R2Ta0------------------------ ------------------------------ITPUB个人空间oK%gA9j0wH
PROGRAMITPUB个人空间2`` o0F:XD m
----------------------------------------------------------------ITPUB个人空间.{+S"jp6vlGi+z
5024                     KYS\kyslogin
kHV@PX9g'g0sqlplus.exe

ITPUB个人空间lG9g?[
SQL> exit
,mdq;F2Q wzpv;Ch4`0Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - PrITPUB个人空间'w/p,s'iNrQua0L7@
oductionITPUB个人空间y
[#lvhM!gO
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

C:\Users\kyslogin>orakill m1data 5024   --在linux下，使用  kill -9 命令

Kill of thread id 5024 in instance m1data successfully signalled.

C:\Users\kyslogin>

N;]E"b*U{kk0C:\Users\kyslogin>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 2 15:02:31 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn **********/**********ITPUB个人空间q^`D Lq!_,yBMm
Connected.ITPUB个人空间 e M'oLw@ g5]HN
SQL> truncate table test_source;

Table truncated.

SQL>

 

按常理到此处已经可以解决资源占用的问题，如果还是无法解决，就需要等待数据库数据回滚结束。