Re: How to check Lock SQL - Please help
- From: Thomas Day <tomday2@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 22 Feb 2005 15:10:46 -0500
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
/
On Tue, 22 Feb 2005 09:33:58 -0800 (PST), Sanjay Mishra
<smishra_97@xxxxxxxxx> 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@xxxxxxxxx> 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
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
