Unanswered: URGENT HELP PLEASE - drop index hangs on SAP
OS = Linux
DBVersion = Oracle10.2.0.2
Need some urgent help advise please on how to get around this problem.
On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.
I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. Can anyone please suggest if there is anything I can check on the database on why it is taking such a long time?
I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ... :-)
Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.
Any response and advise on this will be very much appreciated.
Hi sounds like there is a lock on the table which is blocking the index from being dropped. The dropping of an index should never take more than miliseconds, the rest of the work happens in the background. If you can cancel the drop index operation, stop SAP and then retry the operation. One of the SAP processes most likely has kept a lock on the table which is causing the problem.
Failing that stop/start the database but only as a last resort.