ORA-00054,ORA-00031 resource busy and acquire with NOWAIT 資源佔用處理過程

YallonKing發表於2012-02-08
UIAI
truncate佔用資源解決辦法
SQL> truncate table test_source;
truncate table test_source
               *
ERROR at line 1:
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
--------------------------------------------------------------------------------
SESSION_ID ORACLE_USERNAME
---------- ------------------------------
TEST_SOURCE
       190 **********
 
SQL> select sid, serial#, username, osuser from v$session where sid='190';
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OSUSER
------------------------------
       190       2028 **********
KYS\kyslogin

SQL> conn sys/oracle as sysdba
Connected.
SQL> alter system kill session '190,2028';
alter system kill session '190,2028'
*
ERROR at line 1:
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
------------------------ ------------------------------
PROGRAM
----------------------------------------------------------------
5024                     KYS\kyslogin
sqlplus.exe

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
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>

C:\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 **********/**********
Connected.
SQL> truncate table test_source;
Table truncated.
SQL>
 
按常理到此處已經可以解決資源佔用的問題，如果還是無法解決，就需要等待資料庫資料回滾結束。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-715840/，如需轉載，請註明出處，否則將追究法律責任。

相關文章