RE: wierd locking issue

Unfortunately, based on my experience trying to add indexes to busy tables since the ALTER TABLE is a DDL statement there is an implicit commit before it runs which releases the LOCK TABLE and between the release of the table lock and the execution of the ALTER some other session gains access to the object. Even if you get the table lock you might end up still getting the error. I would still give it a try but it is not a foolproof solution.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kurt Franke Sent: Tuesday, February 26, 2008 6:32 AM To: oracle-l_at_freelists.org; ryan_gaffuri_at_comcast.net Subject: Re: wierd locking issue

you may issue a

LOCK TABLE your_table IN EXCLUSIV MODE;

just before the ALTER TABLE ... ADD/SPLIT/DROP PARTITION ...

in your code to force a wait.

> I have code that adds and drops partitions. I am constantly getting
this error. I am doing a few things to track this down and alleviate it.

>
> 1. at the beginning of the execution of my package I issue:
> alter session set ddl_wait_for_locks=true;
>
> now I thought that with this set to true, I would just wait when
adding or dropping a partition instead of erroring out?
>

regards

kf

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


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