How to rename index which is another tablespace oracle?-CodePudding

I want to rename an index which is in another tablespace.

Is the following SQL (oracle) valid?

ALTER INDEX OLD_INDEX_NAME RENAME TO NEW_INDEX_NAME TABLESPACE OTHER_TABLE_SPACE;

CodePudding user response：

Tablespace doesn't matter; you aren't relocating index, just renaming it.

Sample table in one tablespace:

SQL> create table test (id number) tablespace user_rw;

Table created.

SQL> select table_name, tablespace_name from user_tables where table_name = 'TEST';

TABLE_NAME      TABLESPACE_NAME
--------------- --------------------
TEST            USER_RW

Index on that table, in another tablespace:

SQL> create index i1test on test (id) tablespace rwmig;

Index created.

SQL> select index_name, table_name, tablespace_name
  2  from user_indexes where index_name = 'I1TEST';

INDEX_NAME      TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
I1TEST          TEST            RWMIG

Rename index:

SQL> alter index i1test rename to i1test_new;

Index altered.

Result: index is renamed, tablespace remained the same (why wouldn't it?):

SQL> select index_name, table_name, tablespace_name
  2  from user_indexes where index_name = 'I1TEST_NEW';

INDEX_NAME      TABLE_NAME      TABLESPACE_NAME
--------------- --------------- --------------------
I1TEST_NEW      TEST            RWMIG

SQL>