Кто блокирует объект. Еще раз об этом. Администратору в копилку

dbstalker, 16 марта

Жизнь администратора полна неприятных неожиданностей. Так вот, чтобы неожиданностей было немного меньше, нужно быть готовым к решению некоторым проблем. Дальше представлены НЕ мои коды. Все они взяты с sql.ru. Поэтому, если кто-то заявит о своем авторстве, мы безотлагательно объявим об этом в этом же посте. А пока изучайте, тестируйте, используйте.

Все, кто ждут объект, и кто его держит:

SELECT sn.username, m.sid, m.type, 
        DECODE(m.lmode, 0, 'None', 
                        1, 'Null', 
                        2, 'Row Share', 
                        3, 'Row Excl.', 
                        4, 'Share', 
                        5, 'S/Row Excl.', 
                        6, 'Exclusive', 
                lmode, ltrim(to_char(lmode,'990'))) lmode, 
        DECODE(m.request,0, 'None', 
                         1, 'Null', 
                         2, 'Row Share', 
                         3, 'Row Excl.', 
                         4, 'Share', 
                         5, 'S/Row Excl.', 
                         6, 'Exclusive', 
                         request, ltrim(to_char(m.request, 
                '990'))) request, m.id1, m.id2 
FROM v$session sn, v$lock m 
WHERE (sn.sid = m.sid AND m.request != 0)  --кто ждет
        OR (sn.sid = m.sid 
                AND m.request = 0 AND lmode != 4 
                AND (id1, id2) IN (SELECT s.id1, s.id2 
     FROM v$lock s 
                        WHERE request != 0 
              AND s.id1 = m.id1 
                                AND s.id2 = m.id2) 
                ) –- кто держит объект, который ждут
ORDER BY id1, id2, m.request; 

Больше информации:

select /*+ ordered */ wk.sid sid_wait,
 bs.sid sid_lock, bs.serial# Serial, hk.ctime,
 bs.username||'\'|| bs.osuser||'\'||bs.machine blocker,
 bs.status,
 bs.sql_hash_value sql_hash,
 bs.prev_hash_value Prev_Sql_hash,
 bs.program, bs.module, bs.action, bs.client_info,
 TO_CHAR(bs.logon_time,'hh:mi:ss dd.mm.yyyy') logon_time,
 hk.type,
 case hk.type
   when 'TM' then (select ob.owner || '.' || ob.object_name
                   from dba_objects ob where ob.object_id= hk.id1)
   when 'TX' then (select ob.owner || '.' || ob.object_name ||' / '||
                   dbms_rowid.rowid_create(1, ob.data_object_id,
                   ws.row_wait_file#, ws.row_wait_block#, ws.row_wait_row#)
                   from dba_objects ob where ob.object_id(+)=ws.row_wait_obj#)
 end obj_rowid
FROM
   v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE
     hk.block   = 1
  AND  wk.request  != 0
  AND  wk.TYPE (+) = hk.TYPE
  AND  wk.id1  (+) = hk.id1
  AND  wk.id2  (+) = hk.id2
  AND  hk.sid    = bs.sid(+)
  AND  wk.sid    = ws.sid(+)
  and  bs.lockwait is null
ORDER BY hk.ctime desc

Практически тоже самое, но в другом виде.

Автор – Evostr

SELECT /*+ ORDERED*/
 bs.username "Blocking User", 
 bs.username "DB User", 
 ws.username "Waiting User", 
 bs.sid "SID", 
 ws.sid "WSID", 
 bs.sql_address "address", 
 bs.sql_hash_value "Sql hash", 
 bs.program "Blocking App", 
 ws.program "Waiting App", 
 bs.machine "Blocking Machine", 
 ws.machine "Waiting Machine", 
 bs.osuser "Blocking OS User", 
 ws.osuser "Waiting OS User", 
 bs.serial# "Serial#", 
 DECODE(wk.TYPE, 
	'MR', 'Media Recovery', 		'RT', 'Redo Thread',		'UN', 'USER Name', 
 'TX', 'Transaction', 	'TM', 'DML',	'UL', 'PL/SQL USER LOCK', 
	'DX', 'Distributed Xaction', 'CF', 'Control FILE',	'IS', 'Instance State', 
	'FS', 'FILE SET', 'IR', 'Instance Recovery',	'ST', 'Disk SPACE Transaction', 
	'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 
	'LS', 'LOG START OR Switch',	'RW', 'ROW Wait','SQ', 'Sequence Number', 
	'TE', 'Extend TABLE', 'TT', 'Temp TABLE',	wk.TYPE) lock_type, 
	DECODE(hk.lmode, 0, 'None',	1, 'NULL', 	2, 'ROW-S (SS)', 	3, 'ROW-X (SX)', 
  4, 'SHARE',	5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE', TO_CHAR(hk.lmode)) mode_held, 
 DECODE(wk.request,	0, 'None',	1, 'NULL', 	2, 'ROW-S (SS)',  3, 'ROW-X (SX)', 
  4, 'SHARE',	5, 'S/ROW-X (SSX)',	6, 'EXCLUSIVE', 	TO_CHAR(wk.request)) mode_requested, 
 TO_CHAR(hk.id1) lock_id1, 
	TO_CHAR(hk.id2) lock_id2 
FROM 
   v$lock hk,  v$session bs, 
			v$lock wk,  v$session ws 
WHERE 
     hk.block   = 1 
AND  hk.lmode  != 0 
AND  hk.lmode  != 1 
AND  wk.request  != 0 
AND  wk.TYPE (+) = hk.TYPE 
AND  wk.id1  (+) = hk.id1 
AND  wk.id2  (+) = hk.id2 
AND  hk.sid    = bs.sid(+) 
AND  wk.sid    = ws.sid(+)
ORDER BY 1

Авторам приведенных кодов наш пламенный респект. Отзывайтесь – оракл должен знать своих героев.

 

Новый комментарий

Я не спамер: введите суму 6+4



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Телепрограмма
23 июня, 1 ответа
Турция
23 июня, 4 ответа
Выбор люстры
22 июня, 1 ответа