Re: Newbie: finding out which tables are lock?

Your join doesn't seem logical to me. I am not at a machine where I can crank out a script for you, sorry. I don't see anything in your query (where clause) that identifies anyting that is locked.

I assume you want the sid of the locker, sql associated with that sid, what sid is blocked(if any) and what is the object that is locked?

Try looking at dba_waiters and join that to v$sqlarea or v$sqltext

--
Dave A



> I've been trying to write a script to find out which tables

> are currently locked (e.g someone has not executed a commit after updating

 a


> table)

>

> What I have so far is.

>

> select b.username,c.sid,c.owner,

> c.object,b.lockwait,a.sql_text

> from v$sqltext a,

>      v$session b,

>      v$access  c,

>      v$locked_object d,

>      dba_objects e

> where  a.address=b.sql_address

> and    a.hash_value=b.sql_hash_value

> and    b.sid=c.sid

> and    d.object_id=e.object_id

> and    e.object_name=c.object

> and    c.owner!='SYS'

>

> I then tested this by UPDATE a table without a commit.  When I execute

 this


> script

> it returns nothing.  Can any guru out there see the flaw in the script ?

>

> Thanks in advance.

>

> Andrew

>

>