SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
13 15682 DLSCDB
18 24454 DLSCDB
30 51186 DLSCDB
32 14677 MDNUSER
55 2791 MDNUSER
68 58033 DLSCDB
83 33930 PLAY_CTL
84 20574 DLSCDB
103 2741
116 9356 DLSCDB
119 47792 DLSCDB
SID SERIAL# USERNAME
---------- ---------- ------------------------------
128 832 DLSCDB
131 5825 MDNUSER
135 55165 PLAY_CTL
150 56295 PLAY_CTL
151 22358 PLAY_CTL
154 15261 PLAY_CTL
160 51362 DLSCDB
170 25561 DLSCDB
173 60125 PLAY_CTL
175 7195 DLSCDB
179 16629 MDNUSER
SID SERIAL# USERNAME
---------- ---------- ------------------------------
181 26112 PLAY_CTL
185 8876 PLAY_CTL
191 57970
195 46975 PLAY_CTL
196 23941 MDNUSER
208 18227 MDNUSER
209 20221 DLSCDB
211 36874 PLAY_CTL
219 61922 PLAY_CTL
226 35503 PLAY_CTL
227 7952 MDNUSER
SID SERIAL# USERNAME
---------- ---------- ------------------------------
229 64528 PLAY_CTL
232 16552 DLSCDB
237 20974 MDNUSER
239 56648 DLSCDB
242 53726 DLSCDB
246 30426 DLSCDB
251 21662 DLSCDB
259 25
268 43750
274 19
275 47
SID SERIAL# USERNAME
---------- ---------- ------------------------------
277 3
479 53029 DLSCDB
496 36358 SYS
504 56043 DLSCDB
538 19
540 32 PLAY_CTL
548 21
549 1
552 3
553 9
690 49807 PLAY_CTL
SID SERIAL# USERNAME
---------- ---------- ------------------------------
699 49328 PLAY_CTL
729 15545 PLAY_CTL
743 31837 PLAY_CTL
767 16315 PLAY_CTL
768 35292 PLAY_CTL
789 13707 DLSCDB
828 1
829 15
881 5420 DLSCDB
887 16924 PLAY_CTL
899 10657 DLSCDB
SID SERIAL# USERNAME
---------- ---------- ------------------------------
941 34593 MDNUSER
946 34004 DLSCDB
1048 10655 MDNUSER
1050 11346 DLSCDB
1077 41133 DLSCDB
1079 39874 MDNUSER
1081 1168
1091 25
1092 3
1093 1
1094 1
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1095 1
1096 3
1097 1
1098 1
1099 1
1100 1
1101 1
1102 1
1103 1
1104 1
87 rows selected.
SQL> select distinct sid from v$mystat;
SID
----------
496
SQL> select sid,serial#,username from v$session where sid = 496;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
496 36358 SYS
SQL> select addr, xidusn from v$transaction;
no rows selected
SQL> select sid,type,id1,id2,lmode,request from v$lock;
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
83 AE 100 0 4 0
261 AE 100 0 4 0
277 MR 1 0 4 0
277 MR 2 0 4 0
277 MR 3 0 4 0
277 MR 4 0 4 0
277 MR 5 0 4 0
277 MR 6 0 4 0
277 MR 7 0 4 0
277 MR 8 0 4 0
277 MR 9 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
277 MR 10 0 4 0
277 MR 11 0 4 0
277 MR 12 0 4 0
277 MR 13 0 4 0
277 MR 14 0 4 0
277 MR 15 0 4 0
277 MR 16 0 4 0
277 MR 17 0 4 0
277 MR 18 0 4 0
277 MR 19 0 4 0
277 MR 20 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
277 MR 21 0 4 0
277 MR 201 0 4 0
277 MR 202 0 4 0
277 MR 203 0 4 0
270 AE 100 0 4 0
137 AE 100 0 4 0
181 AE 100 0 4 0
239 AE 100 0 4 0
160 AE 100 0 4 0
122 AE 100 0 4 0
219 AE 100 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
251 AE 100 0 4 0
229 AE 100 0 4 0
277 MR 23 0 4 0
116 AE 100 0 4 0
33 AE 100 0 4 0
128 AE 100 0 4 0
277 MR 22 0 4 0
71 AE 100 0 4 0
135 AE 100 0 4 0
150 AE 100 0 4 0
131 AE 100 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
277 MR 24 0 4 0
232 AE 100 0 4 0
242 AE 100 0 4 0
214 AE 100 0 4 0
151 AE 100 0 4 0
170 AE 100 0 4 0
125 AE 100 0 4 0
237 AE 100 0 4 0
32 AE 100 0 4 0
81 AE 100 0 4 0
18 AE 100 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
22 AE 100 0 4 0
119 AE 100 0 4 0
55 AE 100 0 4 0
196 AE 100 0 4 0
185 AE 100 0 4 0
268 AE 100 0 4 0
154 AE 100 0 4 0
100 AE 100 0 4 0
226 AE 100 0 4 0
175 AE 100 0 4 0
176 AE 100 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
179 AE 100 0 4 0
211 AE 100 0 4 0
30 AE 100 0 4 0
227 AE 100 0 4 0
114 AE 100 0 4 0
38 AE 100 0 4 0
195 AE 100 0 4 0
246 AE 100 0 4 0
173 AE 100 0 4 0
13 AE 100 0 4 0
496 AE 100 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
552 RT 1 0 6 0
504 AE 100 0 4 0
515 AE 100 0 4 0
488 AE 100 0 4 0
479 AE 100 0 4 0
508 AE 100 0 4 0
474 AE 100 0 4 0
540 AE 100 0 4 0
729 AE 100 0 4 0
743 AE 100 0 4 0
768 AE 100 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
699 AE 100 0 4 0
767 AE 100 0 4 0
690 AE 100 0 4 0
1096 XR 4 0 1 0
1096 CF 0 0 2 0
1096 RS 25 1 2 0
1093 AE 100 0 4 0
1077 AE 100 0 4 0
1095 TS 3 1 3 0
1050 AE 100 0 4 0
1101 KT 11261 0 4 0
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
941 AE 100 0 4 0
1095 TS 4 1 3 0
887 AE 100 0 4 0
102 rows selected.
select sid,type,id1,id2,lmode,request from v$lock where TYPE = 'TM';
select sid,type,trunc(id1/power(2,16)) rbs,bitand(id1, to_number('ffff','xxxx'))+0 slot
, id2 req, lmode,request
from v$lock where TYPE = 'TX'
select xidusn ,xidslot,xidsqn from v$transaction where xidsqn = 62035157;
rbs = xidusn
slot = xidsqn
req = xidsqn
select sid,type,rbs,slot,req,lmode,request from (
select sid,type,trunc(id1/power(2,16)) rbs,bitand(id1, to_number('ffff','xxxx'))+0 slot , id2 req, lmode,request from v$lock where TYPE = 'TX')
a,v$transaction b where a.rbs = b.xidusn and a.slot = b.xidsqn and a.req = b.xidsqn
//技术指导
POWER
返回n1的n2次方根。
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
//按位与获取低4字节
SQL> select to_number('ffff','xxxx') from dual;
TO_NUMBER('FFFF','XXXX')
------------------------
65535
select bitand(id1, to_number('ffff','xxxx') from dual;
SQL> select sid,type,trunc(id1/power(2,16)) rbs,bitand(id1, to_number('ffff','xxxx'))+0 slot
2 , id2 req, lmode,request
3 from v$lock where TYPE = 'TX'
4 ;
SID TY RBS SLOT REQ LMODE REQUEST
---------- -- ---------- ---------- ---------- ---------- ----------
293 TX 203 25 66289125 0 6
1160 TX 216 5 75372825 0 4
1571 TX 216 5 75372825 0 4
720 TX 216 5 75372825 0 4
1292 TX 216 5 75372825 0 4
1576 TX 216 5 75372825 0 4
7 TX 216 5 75372825 0 4
294 TX 216 5 75372825 0 4
294 TX 39 21 24708778 6 0
1292 TX 439 6 12969481 6 0
1571 TX 404 8 17025959 6 0
SID TY RBS SLOT REQ LMODE REQUEST
---------- -- ---------- ---------- ---------- ---------- ----------
1576 TX 443 8 11443639 6 0
431 TX 35 6 15830713 6 0
1293 TX 440 9 12806854 6 0
720 TX 196 14 13867745 6 0
1160 TX 38 4 22696603 6 0
7 TX 416 15 17765488 6 0
select object_name ,object_id from user_objects where object_id = 17765488;
select sid,type,trunc(id1/power(2,16)) rbs,bitand(id1, to_number('ffff','xxxx'))+0 slot
, id2 req, lmode,request
from v$lock where TYPE = 'TM'
;
select object_name ,object_id from user_objects where object_id = 59760;