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

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: mark@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Sun, 02 Jan 2005 14:47:41 +0800


Thanks.  Yes, we will  have to go to 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 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.

At 06:19 PM Saturday, you wrote:
>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 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 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 J. Bobak
>"Science is the belief in the ignorance of experts."  --Richard P.

Hemant K Chitale


Other related posts: