Re: How to check Lock SQL - Please help
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: tomday2@xxxxxxxxx
- Date: Tue, 22 Feb 2005 15:37:46 -0500
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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxx> 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
> /
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
