???????????????￡???????????????????????????????????????????????????????
?????????Щ?????????????????????????????????????????????????????λ??????
????1. ?????????
??????黷????Oracle 10.2.0.5 ?????
??????1?????????????
SQL> select sid from v$mystat where rownum=1;
SID
----------
144
SQL> show user
USER is "JINGYU"
SQL> select * from t1 where id=1 for update;
ID          N CONTENTS
---------- ---------- ----------------------------------------
1          1 Alfred Zhao
??????2?????????????
SQL> select sid from v$mystat where rownum=1;
SID
----------
149
SQL> show user
USER is "JINGYU"
SQL> update t1 set contents='Mcdull' where id=1;
????????update??????????????????????????????г??????????????????????????????
??????3?????DBA????
SQL> select sid from v$mystat where rownum=1;
SID
----------
145
SQL> show user
USER is "SYS"
SQL> select sid?? username?? blocking_session from v$session where blocking_session is not null;
SID USERNAME                       BLOCKING_SESSION
---------- ------------------------------ ----------------
149 JINGYU                                      144
SQL> select sid?? serial#?? username from v$session where sid=144;
SID    SERIAL# USERNAME
---------- ---------- ------------------------------
144        102 JINGYU
?????????????????????149?????144???????????????144??serial#???
????????????????????2???????
????1??????144????????????????????????????????
????2??????????λ??144??????????????????????????????
?????????????????2??update??????????г????
????2.RAC????
??????黷????Oracle 10.2.0.5 RAC
?????????RAC???????????????λ?????????????????????????????????????????blocking_instance??μ??ɡ?
???????2?????????????
????select sid from v$mystat where rownum=1;
????select * from t1 where id=1 for update;
???????1?????????????
????select sid from v$mystat where rownum=1;
????update t1 set contents='Mcdull' where id=1;
?????????DBA????
SQL> select sid?? username?? blocking_instance?? blocking_session from gv$session where blocking_session is not null;
SID USERNAME                       BLOCKING_INSTANCE BLOCKING_SESSION
---------- ------------------------------ ----------------- ----------------
129 JINGYU                                         2              129
SQL> select inst_id?? sid?? serial#?? username from gv$session where sid=129;
INST_ID        SID    SERIAL# USERNAME
---------- ---------- ---------- ------------------------------
1        129        617 JINGYU
2        129        207 JINGYU
?????????????????????????????????ū?????????????????????????????????????????????????????2?????;
????SQL> select instance_number from v$instance;
????INSTANCE_NUMBER
????---------------
????2
????SQL> alter system kill session '129??207';
????System altered.
??????ο??????????????????????????
????????
????????????????????????????????????У????????????????????????????????????д???SQL???????????????SQL??????????????SQL????????ó?????
select a.sid blocker_sid?? a.serial#?? a.username as blocker_username?? b.type??
decode(b.lmode??0??'None'??1??'Null'??2??'Row share'??3??'Row Exclusive'??4??'Share'??5??'Share Row Exclusive'??6??'Exclusive') lock_mode??
b.ctime as time_held??c.sid as waiter_sid??
decode(c.request??0??'None'??1??'Null'??2??'Row share'??3??'Row Exclusive'??4??'Share'??5??'Share Row Exclusive'??6??'Exclusive') request_mode??
c.ctime time_waited
from   v$lock b?? v$enqueue_lock c?? v$session a
where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1
order by time_held?? time_waited;
???????????????DBA???????????????????????????????????????о???Щ???????????????????????????????д??SQL????????????????????????á?