SQL*Loader   «Prev 

Move or Relocate Table

ALTER TABLE tablename MOVE
[TABLESPACE tablespacename]
[PCTFREE n]
[PCTUSED n];


ALTER TABLE PRODUCT_OBJ_TABLE 
MOVE TABLESPACE USER_NEXT;


ALTER TABLE CUSTOMER_OBJ_TABLE MOVE
PCTFREE 15;

Location 1 Choose one of these three options. Choosing the TABLESPACE option relocates the table to the specified tablespace. The other choices reorganize the table.
Location 2 This table is moved to a new location.
Location 3 This table is reorganized.

Reorganize tables to remove Fragmentation

Scenario: I want to use the 
alter table move tablespace
command to reorganize tables to remove fragmentation.
Question: Does the "alter table move tablespace" command preserve my 1) index and 2) constraint definitions?
Answer: Oracle has many ways to reorganize tables including
  1. alter table move,
  2. create table as select (CTAS),
  3. dbms_redefinition, and
  4. data pump export import (expdp, impdp).
When using the alter table move tablespace syntax, make sure to define two tablespaces of the same size to receive the tables.
I use the names tablename_front and tablename_back. 
alter table sales move tablespace sales_font;

The alter table move tablespace syntax preserves the index and constraint definitions: The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid.
Therefore, the need to execute step two immediately after step one, which is to rebuild the invalid indexes on the current table.
At the same time an advantage of using the table move procedure is
  1. all constraints are preserved and
  2. index definitions are also saved,
so that re-indexing is possible using the fast index REBUILD method, rather than the slower index DROP and CREATE method.