Menu Close

如何在Oracle中查找锁定的行

lcc0556

1. 查询数据库中锁信息

-- 1. 查询锁对象相关的属性：
SELECT glo.inst_id,      -- 锁存在的实例ID
       glo.session_id,   -- 锁存在的会话ID
       o.owner,          -- 锁对象拥有者
       glo.object_id,    -- 锁对象ID
       DECODE(gl.type,'TM', o.object_name, NULL) object_name,   -- 锁对象名称
       o.subobject_name,    -- 子对象名字，例如分区名称
       o.object_type,       -- 对象类型：表、分区等
       gl.type,    -- 锁的类型
       DECODE(gl.lmode, 0,'0:none',
                        1,'1:NULL',
                        2,'2:SS(Row-Share)',
                        3,'3:SX(Row-X)',
                        4,'4:S(Share)',
                        5,'5:SSX(S/Row-X)',
                        6,'6:X(Exclusive)') lmode, -- 会话持有锁的锁定模式
       DECODE(gl.request, 0,'0:none',
                         1,'1:NULL',
                         2,'2:SS(Row-Share)',
                         3,'3:SX(Row-X)',
                         4,'4:S(Share)',
                         5,'5:SSX(S/Row-X)',
                         6,'6:X(Exclusive)')  request, -- 进程请求锁定的锁定模式
       SYSDATE - gl.ctime/60/60/24 AS lock_start_time, -- 锁开始的时间
       ROUND(gl.ctime/60,0) locked_min   -- 锁定的时间(分钟)
  FROM gv$locked_object glo, gv$lock gl,dba_objects o
 WHERE glo.inst_id = gl.inst_id
   AND glo.session_id = gl.sid
   AND glo.object_id = gl.id1
   AND o.object_id(+) = gl.id1
   -- AND glo.session_id = &session_id
 ORDER BY gl.ctime DESC;

2. 查询事务阻塞源头的会话ID

 SELECT r.root_sid,
        s.serial#,
        r.blocked_num,
        TRUNC(r.avg_wait_seconds) AS avg_wait_seconds,
        s.username,s.status,
        s.event,s.MACHINE,
        s.PROGRAM,
        s.sql_id,
        s.prev_sql_id
   FROM (SELECT root_sid,
                AVG(seconds_in_wait) AS avg_wait_seconds,
                COUNT(*) - 1 AS blocked_num
          FROM (SELECT CONNECT_BY_ROOT sid AS root_sid,
                       seconds_in_wait
                  FROM v$session
                 START WITH blocking_session IS NULL
               CONNECT BY PRIOR SID = blocking_session)
                 GROUP BY root_sid HAVING COUNT(*) > 1
         ) r, v$session s
   WHERE r.root_sid = s.sid;

3. 结合锁会话ID与锁的对象，查询锁对象中具体被锁定的行

CREATE OR REPLACE PROCEDURE locksmith(table_name VARCHAR2) IS
  TYPE tabcurtype IS REF CURSOR;
  TYPE tabrowstype IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  table_cursor tabcurtype;
  rowid_table  tabrowstype;
  row_id       ROWID;
  status       NUMBER;
  aud_sid      NUMBER;
  test_sid     NUMBER;
  test_serial  NUMBER;
  locker_sid   NUMBER;
  locker_name  VARCHAR2(1000);
  message      VARCHAR2(1000);

  resource_busy EXCEPTION;
  PRAGMA EXCEPTION_INIT(resource_busy, -54);

BEGIN
  rowid_table(0) := '0';
  dbms_output.enable(1000000);

  OPEN table_cursor FOR 'select rowid from ' || table_name;
  LOOP
    BEGIN
      FETCH table_cursor INTO row_id;
      EXIT WHEN table_cursor%NOTFOUND;
      SAVEPOINT one_register;

      EXECUTE IMMEDIATE 'select 1 from ' || table_name || ' where rowid =:r for update nowait' USING row_id;

    EXCEPTION
      WHEN resource_busy THEN
        rowid_table(rowid_table.last + 1) := row_id;
    END;

    ROLLBACK TO SAVEPOINT one_register;
  END LOOP;

  CLOSE table_cursor;

  FOR r IN rowid_table.first + 1 .. rowid_table.last
    LOOP
      dbms_output.put_line(rowid_table(r));
    END LOOP;
  ROLLBACK;
END;

执行上述存储过程，获得表中被锁定的行。

Post Views: 401
Tagged ,