Re: How to Lock an Index

You could try

    lock table in exclusive mode;

This will wait until all other sessions have released their locks. However as soon as you have the table locked and try any DDL (such as ALTER INDEX) you lose the table lock, and if any users are queuing for row locks they get in and you get 00054 again.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Sybrand Bakker wrote in message
<949527533.8259.0.pluto.d4ee154e_at_news.demon.nl>...
>You can't lock an index. There is at least one process using the table and
>as the table is in use it is locked. So the solution would be by means of
>v$access kill all processes using this table.
>
>Hth,
>
>--
>Sybrand Bakker, Oracle DBA
>Ted Persky <TPersky_at_sempra.com> wrote in message
>news:3898A124.4A4C992B_at_sempra.com...
>> Hi gurus:
>>
>> Can anyone shed some light on this problem?
>>
>> This morning an index was getting close to its MAXEXTENTS value. When I
>> tried to do an "alter index" to increase MAXEXTENTS by ten, it returned
>> an ORA-00054 error, that the index was busy and that I had issued the
>> command with NOWAIT. I couldn't find a "WAIT" option to the "alter
>> index" command.
>>
>> Since I didn't see a "lock index" command, I manually locked its
>> associated table in exclusive mode. Still, I got the ORA-00054 error.
>> What process could be accessing the index if I've got the table in an
>> exclusive lock? What is the best way to lock the index, so that I can
>> alter the MAXEXTENTS?
>>
>> Sincerely,
>>
>> Ted Persky
>> TPersky_at_sempra.com
>>
>>
>
>
Received on Wed Feb 02 2000 - 16:20:27 CST