How to Resolve ORA-02243 Error Message

  • by

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

The root cause of ORA-02243 may vary in different operations, so the case in this post may not fit your situation.

Tried to move an index to another tablespace by using REBUILD clause of ALTER INDEX statement, but it failed with ORA-02243.

SQL> alter index emp_emp_id_pk tablespace example rebuild online;
alter index emp_emp_id_pk tablespace example rebuild online
                          *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

ORA-02243 means that there's a syntax error in your ALTER INDEX statement, sometimes, it's an order problem among clauses.

In the above, we used both TABLESPACE and REBUILD clauses in the statement.

Solutions

This is a syntactical error, because declaring TABLESPACE clause first before other actions is invalid. We should use REBUILD clause first, then do the rest. For example:

SQL> alter index emp_emp_id_pk rebuild online tablespace example;

Index altered.

Moving ONLINE to the last is also acceptable.

SQL> alter index emp_emp_id_pk rebuild tablespace example online;

Index altered.

Leave a Reply

Your email address will not be published. Required fields are marked *