Re: Row Locking on Oracle 8

Hi,

You cannot get the information about the locked rows from v$ tables, because it's not there. The locking information is stored in the database along with rows. But you can get
the rowids of locked rows via SQL. I wrote a simple procedure for you:

set serveroutput on size 10000
begin
  for r in (select rowid from test ) loop     declare r# rowid;
    begin
      select rowid into r# from test where rowid=r.rowid for update nowait;     exception
      when others then dbms_output.put_line('Locked '||r.rowid );     end;
  end loop;
end;
/

For purity, change "others" keyword to the exception declared after ora-54 and add code dealing with locked rows in the ways you like it.

I'll appreciate some feedback from you to learn why all this was necessary.

Regards,
Karen Abgarian.

Juan Carlos Alonso Lafuente wrote:

> We are developing using Oracle 8 on the NT platform and we would like to
> know how to deal with locks in some specific ways.
>
> In particular, we are using "SELECT ... FOR UPDATE" to lock a row when
> one user wants to access that particular row.
>
> The question is that we would like to know who is blocking one row in a
> table and which particular row (its rowid).
>
> Using v$session and v$lock we get to know who is blocking a particular
> object (a table) but we do not know the row being blocked.
>
> If anyone could help us on this it would be greatly appreciated.
> Carlos Alonso
> La Ley - Actualidad, Legal Publishers.
> Madrid, Spain
Received on Fri Sep 17 1999 - 12:32:50 CDT