Re: How to avoid or handle the ORA-0054s

Mark,

Thanks. Yes, we will have to go to 9.2.0.5 but I would be insisting on testing the application and the indexes -- the vendors who designed the application don't encounter the ORA-600 [12700s] at their test site even in 9.2.0.4 probably because they don't have the same level of concurrent updates as we have in the Live environment [and we're not sure we can create the same level in our own Test environment]

In the meantime, I will attempt using Jonathan Lewis's simple exception-in-a-loop method.

10g is not under consideration currently.

Regards
Hemant
At 06:19 PM Saturday, you wrote:
>Hemant,
>Actually, in 10g, there is "ddl_wait_for_locks", which defaults to
>FALSE. I've not yet experimented with it, so, I won't comment, other
>than to speculate that it will do what you want.
>
>It really sounds like an upgrade to 9.2.0.5 to avoid the rebuilds in the
>first place is the right answer. A while back, someone had a similar
>requirement, and I suggested use of DBMS_LOCK. Use it to create a UL
>enqueue. Write your code so that any code that does DML against the
>table that the index is on takes a share mode lock. Lots of concurrent
>DML would be able to take the share mode lock. When it's time to run
>the rebuild script, have it take an exclusive lock. If there is any DML
>running against the table, the request for exclusive lock will wait,
>queued behind the share locks. When the DML completes, the exclusive
>lock will be granted, and you can do the rebuilds without fear of
>ORA-0054. When the rebuilds complete, release the exclusive lock, and
>any DML which queued behind the exclusive lock will run.
>
>But, I think it's a lot easier to upgrade to 9.2.0.5 or 6. And, if you
>run across any kind of similar requirement in 10g, "ddl_wait_for_locks"
>is probably worth a look.
>
>Hope that helps,
>
>-Mark
>
>
>Mark J. Bobak
>mark_at_bobak.net
>"Science is the belief in the ignorance of experts." --Richard P.
>Feynman
>
>
>--
>http://www.freelists.org/webpage/oracle-l

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

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