Oracle Lock Related Queries

来源：互联网发布：光子嫩肤效果知乎编辑：程序博客网时间：2024/05/21 07:03

Based on Oracle 10g/11g

-- show lock contention-- v$session.ROW_WAIT_OBJ# = dba_objects.OBJECT_ID-- substr('AALha7AALAAApxMAAA',1,6) <=> dbms_rowid.ROWID_OBJECT = dba_objects.DATA_OBJECT_IDSELECT NVL ((SELECT CASE                      WHEN l.BLOCK = 1                        THEN 'Holder'                      WHEN l.request > 0                        THEN 'Waiter'                    END               FROM v$lock l              WHERE lo.session_id = l.SID                AND (   l.BLOCK = 1                     OR l.request > 0))           ,'Locker') ROLE      , se.SID || ',' || se.serial# sid_serial      ,se.blocking_session wait_for      , o.owner || '.' || o.object_name owner_object      ,se.username      ,se.status      ,se.osuser      ,se.machine      ,se.program      ,se.action      ,DECODE (o.TEMPORARY              ,'N', DBMS_ROWID.rowid_create (1                                            ,o.data_object_id                                            ,se.row_wait_file#                                            ,se.row_wait_block#                                            ,se.row_wait_row#                                            )              ,'Y', NULL              ) row_id      ,sq.sql_text  FROM v$locked_object lo      ,dba_objects o      ,v$session se      ,v$sql sq WHERE lo.object_id = o.object_id   AND lo.session_id = se.SID   AND se.sql_address = sq.address(+)   AND se.sql_hash_value = sq.hash_value(+);

-- sql to kill locked sessionALTER SYSTEM DISCONNECT SESSION '263,48203' IMMEDIATE;ALTER SYSTEM KILL SESSION '276,27825' IMMEDIATE;