本文是完全转自eygle老师的，目的是为了自己学习，绝无他用。如果涉及侵权问题，请联系我删除，我会在文章中注明原文链接。。。
1、如何在其他Session跳过被锁定的记录
原文链接：http://www.eygle.com/archives/2005/03/eciouaeeeusessi.html
有人问到这样的问题:如果一个用户对记录加锁，如何使其他用户看不到这条记录。
此前并未注意到这样一个用法（需要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>
|
2、使用SET TRANSACTION给事务命名
原文链接：http://www.eygle.com/archives/2005/03/eoaset_transact.html
以前没有注意的命令，可以使用SET TRANSACTION给事务命名。
v$transaction中的name字段代表的正是:Name of a named transaction。
[oracle@jumper bdump]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 14 23:17:21 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> set transaction name 'Update Dept Table';
Transaction set.
SQL> select * from scott.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> update scott.dept set LOC='Home' where deptno=35;
1 row updated.
SQL> select xidusn,xidslot,xidsqn,used_ublk,used_urec,name from v$transaction;
XIDUSN XIDSLOT XIDSQN USED_UBLK USED_UREC NAME
---------- ---------- ---------- ---------- ---------- --------------------
3 17 9120 1 1 Update Dept Table
|
要注意，必须是事务开始之前，如果已经开始了事务，但是没有提交（也就是还没有完成）这样就不能再给事务命名了。
时间： 2024-10-12 17:24:57