ORA-02243
The root cause of ORA-02243 may vary in different operations. Let's see some cases.
LOB INDEX
In some situations, you may mistake a LOB index for a normal index, then perform an index rebuilding and got ORA-02243.
SQL> alter index erpapp.SYS_IL0000301498C00003$$ rebuild tablespace example;
alter index erpapp.SYS_IL0000301498C00003$$ rebuild tablespace example
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
The object name looks like a system-generated index, but it's a LOB-based index (LOBINDEX) actually. You can't move (rebuild) it like a normal index.
Solutions
To move a LOB column including its segment and index to another tablespace, you have some other ways to do it.
REBUILD ONLINE
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.
Or move ONLINE to the last.
SQL> alter index emp_emp_id_pk rebuild tablespace example online;
Index altered.
It's also acceptable.
There're more options to move or rebuild an index.