How to check Lock SQL - Please help 2005-02-22 - By Sanjay Mishra
THomas
Thanks for the message. Actually this is not going to work in mine case. Here
is little more.
I want to add new partition into the tableA and getting the Resource Busy
error and so partition is not added. Mine problem is that the tableA is heavily
accessed whole day and every second, I am getting 20-50records into the tableA
which is showing the lock. There are five session running the same process
which is inserting data into the table and those session are having are static
and connection is always maintained.
I tried to run select t.sql_text from v$session s,v$sqltext t where s.sql_hash
_value = t.hash_value and s.sid in (29,30,31,32,33)
I am trying to run it continously and getting only the insert into several
table.
The TableA has 4 FK constraints and all are indexed
ANy idea or suggestions
Thomas Day <tomday2@(protected)> 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" 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
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 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
-- ---- ---- ---- ---- ---- -----
Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'
--
http://www.freelists.org/webpage/oracle-l
|
|
