梦想有多远

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt because a resource was unavailable for a LOCK TABLE or SELECT FOR UPDATE command.

应该是要执行的语句涉及到资源有锁的问题，查看一下，（一般都是因为有事务未提交）

select object_name,machine,
s.program,s.sid,s.serial#,p.spid
from v$locked_object l,dba_objects o ,v$session s ,v$process p
where l.object_id=o.object_id
and l.session_id=s.sid
and s.paddr=p.addr;

找到sid,serial#
alter system kill session ’sid,serial#’;
如果遇到ora-00031

(全文…)

处理Oracle数据库中的坏块[zt]

===========================================================

作者: xsb(http://xsb.itpub.net)
发表于:2006.11.08 14:06
分类: Oracle
出处：http://xsb.itpub.net/post/419/227779
—————————————————————

主要介绍如何去处理在Oracle数据库中出现坏块的问题，对于坏块产生在不同的对象上，处理的方法会有所不同，本文将大致对这些方法做一些介绍。

ref: 如何处理Oracle数据库中的坏块问题[zt]

使用Oracle9i的blockrecover新特性修复数据库中的坏块

一：什么是数据库的坏块

首先我们来大概看一下数据库块的格式和结构

数据库的数据块有固定的格式和结构，分三层：cache layer，transaction layer，data layer。在我们对数据块进行读取写入操作的时候，数据库会对要读写的数据块做一致性的检查，其中包括：数据块的类型、数据块的地址信息、数据块的SCN号以及数据块的头部和尾部。如果发现其中有不一致的信息，那数据库就会标记这个数据块为坏块了。数据库的坏块分为两种，逻辑坏块和物理坏块。

(全文…)

在Oracle10g中可以设置commit(immediate|batch|wait|nowait)的行为来做到在commit之后，控制权立刻返回给用户，而Oracle会在恰当的时候唤醒LGWR，批量更新online redo log文件。
Ref: http://www.itpub.net/viewthread.php?tid=884483&extra=&page=2

IMMEDIATE - The commit “prods” the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.

BATCH - The writes to the redo logs are buffered.

WAIT - The commit command is synchronous. It doesn’t return until the relevant redo information is written to the online redo log.

NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log

可以有N种组合。
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’WAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’NOWAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH,WAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH,NOWAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE,WAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE,NOWAIT’;

SQL>CREATE TABLE commit_test (
id NUMBER(10),
description VARCHAR2(50),
CONSTRAINT commit_test_pk PRIMARY KEY (id)
);
SQL> DECLARE
PROCEDURE do_loop (p_type IN VARCHAR2) AS
2 3 l_start NUMBER;
4 l_loops NUMBER := 1000;
5 BEGIN
6 EXECUTE IMMEDIATE ‘ALTER SESSION SET COMMIT_WRITE=”’ || p_type || ””;
7 EXECUTE IMMEDIATE ‘TRUNCATE TABLE commit_test’;
8
9 l_start := DBMS_UTILITY.get_time;
10 FOR i IN 1 .. l_loops LOOP
11 INSERT INTO commit_test (id, description)
12 VALUES (i, ‘Description for ‘ || i);
COMMIT;
13 14 END LOOP;
15 DBMS_OUTPUT.put_line(RPAD(’COMMIT_WRITE=’ || p_type, 30) || ‘: ‘ || (DBMS_UTILITY.get_time - l_start));
END;
BEGIN
16 17 18 do_loop(’WAIT’);
19 do_loop(’NOWAIT’);
do_loop(’BATCH’);
20 21 do_loop(’IMMEDIATE’);
do_loop(’BATCH,WAIT’);
22 23 do_loop(’BATCH,NOWAIT’);
do_loop(’IMMEDIATE,WAIT’);
24 25 do_loop(’IMMEDIATE,NOWAIT’);
END;
26 27 /
COMMIT_WRITE=WAIT : 286
COMMIT_WRITE=NOWAIT : 87
COMMIT_WRITE=BATCH : 53
COMMIT_WRITE=IMMEDIATE : 91
COMMIT_WRITE=BATCH,WAIT : 268
COMMIT_WRITE=BATCH,NOWAIT : 19
COMMIT_WRITE=IMMEDIATE,WAIT : 284
COMMIT_WRITE=IMMEDIATE,NOWAIT : 36

PL/SQL procedure successfully completed.

ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH,WAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH,NOWAIT’;
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE,WAIT’;

就三种有效.