Oracle中锁定行的查找方法

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;

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;

SQL> exec locksmith('TEST');

AAAM1tAAEAAAANWAAB

AAAM1tAAEAAAANWAAC

PL/SQL procedure successfully completed