How to check Lock SQL - Please help 2005-02-22 - By Jacques Kilchoer
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@(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
/
--
http://www.freelists.org/webpage/oracle-l
|
|
