Oracle中锁定行的查找方法

锁定行的查找

 

要查找session锁定的行,可以有两种方式:

1.       通过v$session中的row_wait_row#查询

set serverout on size 1000000

set lines 132

declare

  cursor cur_lock is

         select sid,id1,id2,inst_id, ctime

           from gv$lock

          where block = 1;

  vid1       number;

  vid2       number;

  cursor cur_locked is

         select sid, inst_id, ctime

           from gv$lock

          where id1 = vid1

            and id2 = vid2

            and block <> 1;

  vlocks     varchar2(30);

  vsid1      number;

  vobj1      number;

  vfil1      number;

  vblo1      number;

  vrow1      number;

  vrowid1    varchar2(20);

  vcli1      varchar2(64);

  vobj2      number;

  vfil2      number;

  vblo2      number;

  vrow2      number;

  vrowid2    varchar2(20);

  vcli2      varchar2(64);

  vobjname   varchar2(30);

  vlocked    varchar2(30);

  ctim1      number;

  ctim2      number;

begin

dbms_output.put_line('=====================================================');

dbms_output.put_line('Blocking lock list.');

dbms_output.put_line('=====================================================');

dbms_output.put_line('Block / Is blocked         SID        INST_ID OBJECT                         TIME(secs) ROWID              CLIENT_IDENTIFIER');

dbms_output.put_line('-------------------------  ---------  ------- ------------------------------ ---------- ------------------ -----------------');

  for c1 in cur_lock loop

      vid1 := c1.id1;

      vid2 := c1.id2;

      select username,sid,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,client_identifier

                 into vlocks,vsid1,vobj1,vfil1,vblo1,vrow1,vcli1

                 from gv$session where sid = c1.sid and inst_id = c1.inst_id;

      if vobj1 = -1 then

         vobjname := 'UNKNOWN';

      else

         select name into vobjname from sys.obj$ where obj# = vobj1;

         select decode(vrow1,0,'MANY ROWS',dbms_rowid.rowid_create(1, vobj1, vfil1, vblo1, vrow1)) into vrowid1 from dual;

      end if;

      dbms_output.put_line(rpad(vlocks,25) || ' ' ||

                           to_char(vsid1,'999999999') || ' ' ||

                           to_char(c1.inst_id,'9999999') || ' ' ||

                           rpad(vobjname,30) || ' ' ||

                           to_char(c1.ctime,'999999999') || ' ' || rpad(vrowid1,18) || ' ' || vcli1);

      for c2 in cur_locked loop

          select username, row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#

            into vlocked, vobj2, vfil2, vblo2, vrow2

            from gv$session where sid = c2.sid and inst_id = c2.inst_id;

          if vobj2 = -1 then

             vobjname := 'UNKNOWN';

          else

             select name into vobjname from sys.obj$ where obj# = vobj2;

             select decode(vrow2,0,'MANY ROWS',dbms_rowid.rowid_create(1, vobj2, vfil2, vblo2, vrow2)) into vrowid2 from dual;

          end if;

          dbms_output.put_line(chr(9) || '--> ' || rpad(vlocked,12) || ' ' ||

                 to_char(c2.sid,'999999999') || ' ' ||

                 to_char(c2.inst_id,'9999999') || ' ' || rpad(vobjname,30) || ' ' ||

                 to_char(c2.ctime,'999999999') || ' ' || rpad(vrowid2,18) || ' ' || vcli2 ) ;

      end loop;

  end loop;

commit;

end;

 

 

这种查询方式的局限是只能查询出来一行,如果是多行,则row_wait_row#返回的是锁定的开始行。

 

2.       通过for update查询所有加锁行

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;

 

使用nowait在表中循环所有记录,判断出所有加锁的行。

 

SQL> exec locksmith('TEST');

 

AAAM1tAAEAAAANWAAB

AAAM1tAAEAAAANWAAC

 

PL/SQL procedure successfully completed

 

 

 

 

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值