Overview

This is a scenario for changing the Tablespace of an already created INDEX.

Method

1. Create Test Data

Create the Tablespace, Table, and Index needed for testing.

SQL> create tablespace ts1 datafile 'ts1.dtf' size 10M autoextend off;

Tablespace 'TS1' created.

SQL> create table test_t1 (c1 number, c2 varchar(10));

Table 'TEST_T1' created.

SQL> create index test_idx on test_t1 (c1);

Index 'TEST_IDX' created.

2. Check the Index

Check the Tablespace of the created Index.

SQL> select index_name, table_name, tablespace_name
     from user_indexes;
 

INDEX_NAME     TABLE_NAME   TABLESPACE_NAME
-------------- ------------ -----------------
TEST_IDX       TEST_T1      USR

3. Check Tablespaces

Check the list of currently created Tablespaces.

SQL> select tablespace_name from dba_tablespaces

TABLESPACE_NAME
---------------------------------------------------------------------
SYSTEM
UNDO
TEMP
USR
SYSSUB
TS1

6 rows selected.

4. Perform INDEX REBUILD

Perform a rebuild on the Index for which you want to change the Tablespace. Specify the tablespace at this time.

-- ALTER INDEX [index name] rebuild tablespace [tablespace name];
SQL> ALTER INDEX TEST_IDX rebuild tablespace ts1;

Index 'TEST_IDX' altered.

Caution

When rebuilding an Index, DML operations (INDEX / UPDATE / DELETE, and sometimes SELECT) using that index may be delayed or fail.

5. Check the Index

Check the changed Tablespace.

SQL> select index_name, table_name, tablespace_name
     from user_indexes;
 

INDEX_NAME     TABLE_NAME   TABLESPACE_NAME
-------------- ------------ -----------------
TEST_IDX       TEST_T1      TS1