RE: How to check Lock SQL - Please help

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 22 Feb 2005 16:46:58 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87C7C@irvmbxw02>

I'm not sure what you mean when you say "I am trying to run it continously and getting only the insert into several table." Do you mean that you don't see the inserts into table A, only inserts into other tables?
Does table A have foreign key constraints, or are their foreign key constraints on other tables referencing table A, or does table A have both (foreign key constraints AND referencing foreign key constraints)? Try the alter table add partition in a PL/SQL loop

declare

   resource_busy exception ;
   pragma exception_init (resource_busy, -00054) ;    partition_added boolean := false ;
begin

   while not partition_added
   loop

      begin
         execute immediate 'alter table SCHEMA.A add partition
PARTITION_NAME' ;
         partition_added := true ;
      exception
         when resource_busy
         then
            null ;
         when others
         then
            raise ;
      end ;
      if not partition_added
      then
         dbms_lock.sleep (1) ;
      end if ;

   end loop ;
end ;
/

-----Original Message-----
Sanjay Mishra

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

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
/

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

Received on Tue Feb 22 2005 - 19:49:59 CST