Tables can be relocated from one tablespace to another without using the old export/import method or the "create table as select" method. This is possible using the "alter table move" command. This command will not work if there is a LONG or LONG RAW datatype in the table that you want to move.
During the relocation process, you can choose to assign new storage parameters to the table. This is much easier than using export/import to rebuild a table from scratch using new storage values. The advantage of this method over the "create table as select" method is that all the table dependencies will be preserved (constraints, grants, triggers). Indexes on this table will be marked UNUSABLE after the table has been moved and will have to be rebuilt or recreated.
Take for example, a table called DAILY_LOG with the "log_key" column being the primary key. Let us also create a unique index
on the "entered_by" column.
SQL> create table daily_log (log_key number not null, entered_by varchar2(50),
2 log_text varchar2(4000));
Table created.
SQL> alter table daily_log add constraint daily_log_pk primary key (log_key);
Table altered.
SQL> create unique index entered_by_idx on daily_log(entered_by);
Index created.
SQL> select index_name, table_name, status from user_indexes;
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
DAILY_LOG_PK DAILY_LOG VALID
ENTERED_BY_IDX DAILY_LOG VALID
The table and indexes exist in the TOOLS tablespace. I want to move the table from TOOLS to the USERS tablespace.
SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE",
2 initial_extent, next_extent from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE INITIAL_EXTENT NEXT_EXTENT
--------------- ------------------ ---------------- ---------- -------------- -----------
DAILY_LOG TABLE TOOLS .5 524288 524288
DAILY_LOG_PK INDEX TOOLS .5 524288 524288
ENTERED_BY_IDX INDEX TOOLS .5 524288 524288
CASE A: The following command will maintain the current storage settings for the table while rebuilding it in the USERS tablespace. Both the indexes on the table will show a status of unusable and will need to be rebuilt or recreated.
SQL> alter table DAILY_LOG move tablespace users;
Table altered.
SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE",
2 initial_extent, next_extent from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE INITIAL_EXTENT NEXT_EXTENT
--------------- ------------------ ---------------- ---------- -------------- -----------
DAILY_LOG TABLE USERS .5 524288 524288
DAILY_LOG_PK INDEX TOOLS .5 524288 524288
ENTERED_BY_IDX INDEX TOOLS .5 524288 524288
CASE B: If I wanted to change the storage settings for the table during the move, I would use the storage clause like shown below. Again, the indexes would show a status of UNUSABLE.
SQL> alter table daily_log move tablespace USERS storage (initial 40k next 40k pctincrease 0);
Table altered.
SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE",
2 initial_extent, next_extent from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE INITIAL_EXTENT NEXT_EXTENT
--------------- ------------------ ---------------- ---------- -------------- -----------
DAILY_LOG TABLE USERS .5 40960 40960
DAILY_LOG_PK INDEX TOOLS .5 524288 524288
ENTERED_BY_IDX INDEX TOOLS .5 524288 524288
CASE C: If I just wanted to rebuild the table in the original tablespace (TOOLS), but just change the storage parameters, I would use the STORAGE clause, but not the TABLESPACE clause. The indexes are again marked UNUSABLE.
SQL> alter table daily_log move storage (initial 40k next 40k pctincrease 0);
Table altered.
Our table has been rebuilt in the same tablespace with different storage parameters.
SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE",
2 initial_extent, next_extent from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE INITIAL_EXTENT NEXT_EXTENT
--------------- ------------------ ---------------- ---------- -------------- -----------
DAILY_LOG TABLE TOOLS .5 40960 40960
DAILY_LOG_PK INDEX TOOLS .5 524288 524288
ENTERED_BY_IDX INDEX TOOLS .5 524288 524288
After the move, the indexes on the table will be marked unusable and will have to be rebuilt. Remember that bitmap indexes cannot be rebuilt online.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
DAILY_LOG_PK UNUSABLE
ENTERED_BY_IDX UNUSABLE
SQL> alter index daily_log_pk rebuild online;
Index altered.
I can even change the storage settings and tablespace for the index during the rebuild.
SQL> alter index entered_by_idx rebuild storage (initial 40k next 40k pctincrease 0) tablespace users online;
Index altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
DAILY_LOG_PK VALID
ENTERED_BY_IDX VALID
Now that the table has been moved and the indexes have been rebuilt, you are all set to go.
SQL> select segment_name, segment_type, tablespace_name, bytes/1024/1024 "SIZE",
2 initial_extent, next_extent from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SIZE INITIAL_EXTENT NEXT_EXTENT
--------------- ------------------ ---------------- ---------- -------------- -----------
DAILY_LOG TABLE USERS .5 40960 40960
DAILY_LOG_PK INDEX TOOLS .5 524288 524288
ENTERED_BY_IDX INDEX USERS .5 40960 40960