SQL Optimizer picked up virtual indexes from my database in the 'Delete Virtual Indexes' window, but when I choose to delete them, I get the following error:
"Unable to drop the following index(es)"
The backend code, used to capture and handle virtual indexes that are accidentally left behind, is too board. It also captured ones that are not from SQL Optimizer (i.e. the BIN$ ones). Some of these other virtual indexes you may not have the rights to delete and SQL Optimizer does not know that.
STATUS: Waiting for fix in a future release of SQL Optimizer
WORKAROUND:
1. Run the following query to find the virtual indexes in question
SELECT /*+ RULE */
OBJ.OBJECT_NAME,
OBJ.CREATED
FROM SYS.ALL_OBJECTS OBJ,
SYS.ALL_IND_COLUMNS COL
WHERE OBJ.OWNER = SYS_CONTEXT ('USERENV', 'SESSION_USER')
AND OBJ.OBJECT_TYPE = 'INDEX'
AND OBJ.OWNER = COL.INDEX_OWNER
AND OBJ.OBJECT_NAME = COL.INDEX_NAME
AND NOT EXISTS (SELECT '*'
FROM SYS.ALL_INDEXES IND2
WHERE OBJ.OWNER = IND2.OWNER
AND OBJ.OBJECT_NAME = IND2.INDEX_NAME)
ORDER BY 1,2
NOTE: A virtual index is not a real index, so it will only exist in the ALL_OBJECTS view, but not ALL_INDEXES.
2. After you get the list of virtual index, drop it manually with the following query
‘DROP INDEX schema.index_name’
