ORA-01418
Tried to alter an index, but it failed with ORA-01418.
SQL> alter index emp_emp_id_pk rebuild;
alter index emp_emp_id_pk rebuild
*
ERROR at line 1:
ORA-01418: specified index does not exist
ORA-01418 means that SQL engine cannot find the index you specified, you have to verify the index name or login as the owner of the index.
Let's see what user currently is.
SQL> show user
USER is "OE"
And then we check who the owner of the index is.
SQL> column owner format a10;
SQL> select owner from all_indexes where index_name = 'EMP_EMP_ID_PK';
OWNER
----------
HR
OK, the owner is not the current user.
Solution
You have 2 options to solve ORA-01418.
Login as the Owner
The owner has the right to change or rebuild its index.
SQL> show user
USER is "HR"
SQL> alter index emp_emp_id_pk rebuild;
Index altered.
Grant ALTER ANY INDEX Privilege
For those who wants to alter other's index, we should grant it ALTER ANY INDEX system privilege.
SQL> show user
USER is "SYSTEM"
SQL> grant alter any index to oe;
Grant succeeded.
Let's see the result.
SQL> show user
USER is "OE"
SQL> alter index hr.emp_emp_id_pk rebuild;
Index altered.
Don't forget to qualify the index name with the owner name.