Hello Pepak,

>>>> Indices are not fixed in use by a stored procedure, UNLESS you use a
>>>> PLAN clause in the SQL statements.
>>>>
>>>>
>>> I don't. But I can't imagine any other reason why I would get that
>>> "index unexpectedly deleted" when an application tries:
>>>
>>> SELECT * FROM some_procedure
>>>
>>
>> I believe this message comes when you have the stored procedure loaded
>> in the metadata cache (as done by the Firebird server), executed once so
>> a particular PLAN is created and then delete the index.
>>
> You are correct - when I shutdown the server after index deletion, the
> error disappears. Now I wonder - can I somehow invalidate the metadata
> cache using SQL, or possibly using API itself? My servers are not always
> easily accessible for shutdown and restart.

Detaching all current sessions (for that database) should do the trick, this
will unload the database on the server and therefor clear the metadata
cache.

It's always a bad idea to do metadata changes while applications are still
running.

Shutting down the database to single user mode would certainly be preferred.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com