SESSION 1：
SQL> select * from v$mystat where rownum<2;
SID STATISTIC# VALUE
---------- ---------- ----------
1 0 0
SQL> select * from a1;
ID FLAG1 FLAG2
---------- ---------- ----------
1 a a1
1 b b1
SQL> update a1 set id=2 where flag1='a';
1 row updated.
SESSION 2：
SQL> update a1 set id=2 where flag1='a';
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
35000FE8 35001014 12 XR 4 0 1 0 208 0
3500105C 35001088 12 RD 1 0 1 0 208 0
350010D0 350010FC 12 CF 0 0 2 0 208 0
350011B8 350011E4 12 RS 25 1 2 0 204 0
3500122C 35001258 1 AE 100 0 4 0 123 0
350012A0 350012CC 11 RT 1 0 6 0 204 0
35001314 35001340 10 PW 1 0 3 0 200 0
35001388 350013B4 13 TS 3 1 3 0 201 0
350013FC 35001428 15 AE 100 0 4 0 195 0
35001470 3500149C 10 MR 1 0 4 0 202 0
350014E4 35001510 10 MR 2 0 4 0 202 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
35001558 35001584 10 MR 3 0 4 0 202 0
350015CC 350015F8 10 MR 4 0 4 0 202 0
35001640 3500166C 10 MR 5 0 4 0 202 0
350016B4 350016E0 10 MR 201 0 4 0 202 0
35001728 35001754 25 AE 100 0 4 0 180 0
3500179C 350017C8 26 AE 100 0 4 0 94 0
35001810 3500183C 17 AE 100 0 4 0 177 0
35001884 350018B0 26 TX 393229 9322 0 6 92 0
350018F8 35001924 31 AE 100 0 4 0 9 0
00FF8BF4 00FF8C24 26 TM 75517 0 3 0 92 0
00FF8BF4 00FF8C24 1 TM 75517 0 3 0 103 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
33140924 33140964 1 TX 393229 9322 6 0 103 1
23 rows selected.
SQL> select * from v$lock where sid in (1,26);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
3500122C 35001258 1 AE 100 0 4 0 332 0
3500179C 350017C8 26 AE 100 0 4 0 303 0
35001884 350018B0 26 TX 393229 9322 0 6 301 0
01015210 01015240 26 TM 75517 0 3 0 301 0
01015210 01015240 1 TM 75517 0 3 0 312 0
33140924 33140964 1 TX 393229 9322 6 0 312 1
6 rows selected.
SQL> create table a as select * from v$lock where sid in (1,26);
Table created.
SQL> create table b as select * from v$lock where sid in (1,26);
Table created.
SQL> select hold.sid, block.sid from a hold ,b block
where hold.id1=block.id1
and block.request<>0
and hold.sid<>block.sid 2 3 4
5 ;
SID SID
---------- ----------
1 26