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

原创 Linux操作系统作者：YallonKing 时间：2012-02-08 15:01:09 0 删除编辑

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

SQL> conn **********/**********
Connected.
SQL> truncate table test_source;

Table truncated.

SQL>

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-715840/，如需转载，请注明出处，否则将追究法律责任。

上一篇：关于索引聚簇因子（CLUSTERING_FACTOR）引起的sql优化过程

下一篇： sqlldr配合shell解决sqlldr 控制文件中引入变量的方案

请登录后发表评论登录

全部评论

YallonKing

注册时间：2011-08-07

博文量
72
访问量

247095

ITPub博客

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

最新文章