Oracle Life: March 4, 2005 Archives

March 4, 2005

如何在其他Session跳过被锁定的记录

作者:eygle

出处:http://blog.eygle.com

有人问到这样的问题:如果一个用户对记录加锁，如何使其他用户看不到这条记录。
此前并未注意到这样一个用法（需要8.1.7.4.16以上的版本支持）:

[oracle@jumper oracle]$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Mar 4 11:59:46 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        33 ORACLE         beijing
        35 Oracle         Beijing
        36 Oracle         Beijing
        37 Oracle         Beijing

8 rows selected.

SQL> select * from dept where  deptno =10 for update;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SQL> !
[oracle@jumper oracle]$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Mar 4 12:00:14 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select * from dept for update skip locked;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        33 ORACLE         beijing
        35 Oracle         Beijing
        36 Oracle         Beijing
        37 Oracle         Beijing

7 rows selected.

SQL>

存此备忘。

Posted by eygle at 9:40 PM | Comments (5)

如何重建UNDO TABLESPACE

作者:eygle

出处:http://blog.eygle.com

纯属笔记。

MSN上有朋友问到，在迁移(同平台)的时候由于空间问题，他不打算要现在的UNDO文件，想要重建一个。他打算使用_corrupted_rollback_segments参数。

现在他拥有一个冷备份。
拥有冷备份，那么我们知道这个操作是很简单的，也不用隐含参数:

C:\Documents and Settings\gqgai>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Mar 4 20:55:59 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

20:55:59 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
20:56:14 SQL> startup
ORACLE instance started.

Total System Global Area   59842188 bytes
Fixed Size                   454284 bytes
Variable Size              33554432 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORADATA\EYGLE\UNDOTBS01.DBF'


20:59:28 SQL> alter database datafile 'D:\ORADATA\EYGLE\UNDOTBS01.DBF' offline drop;

Database altered.

Elapsed: 00:00:00.00
20:59:34 SQL> alter database open;

Database altered.

Elapsed: 00:00:02.04
20:59:40 SQL> select name from v$datafile;

NAME
-------------------------------------------------------
D:\ORADATA\EYGLE\SYSTEM01.DBF
D:\ORADATA\EYGLE\UNDOTBS01.DBF
D:\ORADATA\EYGLE\PERFSTAT01.DBF
D:\ORADATA\EYGLE\EYGLE.DBF

21:00:55 SQL> create undo tablespace undotbs2
21:01:03   2  datafile 'd:\oradata\eygle\undotbs2.dbf' size 10M;

Tablespace created.

Elapsed: 00:00:02.02

21:06:29 SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS2' ;

System altered.

Elapsed: 00:00:00.01

需要注意的是，如果使用pfile而不是spfile，我以前的一个案例就可以参考:
Oracle诊断案例-Spfile案例一则

Posted by eygle at 9:28 PM | Comments (4)