Re: How to check Lock SQL - Please help

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

On Tue, 22 Feb 2005 09:33:58 -0800 (PST), Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
> Hi
>
> I think that mine previous question is not suitably
> explained by me and so I didn't get any response and
> so I though that I will give some more facts
>
> 1) I check the program running at the back end and
> found that it is only doing insert by tracing the
> program.
>
> So if I am getting Resource Busy error, can I get the
> sql from dictionary view which can tell as what
> particular SQL is making the lock as then I can pass
> it to Development group with more suggestion
>
> THanks to all
> Sanjay
>
> --- Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
>
> > Dear friend
> >
> > We have deployed a production change today. Change
> > incloves a Pro*C program running at backed from
> > Application server and doing some insert into the
> > table 1,2,3 based on certain condition. This is now
> > taking an exclusive index on the Table1 Primary Key
> > Partitioned index. Table1 is partition Locally and
> > new
> > partition are created daily. Due to lock, I am not
> > able to add partition. Can somebody point as what
> > need
> > to be checked.
> >
> > Table1 is having One primary Key and one Foreign
> > Key.
> > Table1 is partitioned Locally. It has exclusive lock
> > on table1 Primary Key index partiton and Row
> > exclusinve for table partition.
> >
> > Any comment
> > TIA
> > sanjay
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> > protection around
> > http://mail.yahoo.com
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>

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