Re: How to check Lock SQL

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Tue, 22 Feb 2005 12:58:00 -0800 (PST)
Message-ID: <20050222205801.22400.qmail@web51309.mail.yahoo.com>

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_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" 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

Received on Tue Feb 22 2005 - 16:01:00 CST

Re: How to check Lock SQL - Please help