Re: How to check Lock SQL - Please help

From: Paul Drake <bdbafh_at_gmail.com>
Date: Tue, 22 Feb 2005 15:37:46 -0500
Message-ID: <910046b4050222123731a96808@mail.gmail.com>

            from v$lock l,
                 *

ERROR at line 14:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

that is an expensive statement to execute. I think that some caution is in order before one directs that at a live system.

Paul

On Tue, 22 Feb 2005 15:10:46 -0500, Thomas Day <tomday2_at_gmail.com> wrote:
> Are your primary key and foreign key expressed through indexes?
>
> You also might try the script below.
>
> I'm sorry that I can't understand your problem any better but I hope this helps.
>
> --blocker.sql
> /*
> Finds (most of the time) the SQL that is locking a row
> */
> -- Posted by "Mark Leith" <mark_at_cool-tools.co.uk> on Oracle-L
> -- from: (www.cool-tools.co.uk >Support > User Defined Collections > BLOCKER)
> select l.sid sid,
> s.username username,
> s.program program,
> t.sql_text,
> u.name owner,
> o.name object,
> l.type type,
> lmode,
> decode (lmode,1,'NULL',2,'Row Share',3,'Row
> Exclusive',4,'Share',5,'Share Row',6,'Exclusive') mode_desc,
> request,
> decode (request,1,'NULL',2,'Row Share',3,'Row
> Exclusive',4,'Share',5,'Share Row',6,'Exclusive') request_desc
> from v$lock l,
> v$session s,
> sys.obj$ o,
> sys.user$ u,
> v$sqltext t
> where l.type in ('RW','TM','TX','UL')
> and l.sid=s.sid(+)
> and l.id1 = o.obj# (+)
> and o.owner#=u.user#(+)
> and s.sql_hash_value = t.hash_value
> and lmode > 0
> /

>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 22 2005 - 15:40:46 CST