DBA Sensation

December 5, 2008

Moving Table/Index to a New Segment or Tablespace

Filed under: [System Performance tuning] — zhefeng @ 11:54 am

The following statement moves the my_table to a new segment, specifying new storage parameters:

ALTER TABLE my_table MOVE
STORAGE ( INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0 );

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

To move a TABLE TO another TABLESPACE, issue the following command:
ALTER TABLE [table_name] MOVE TABLESPACE [new_tablespace];

To move an INDEX, use the following command:
ALTER INDEX [index_name] REBUILD TABLESPACE [new_tablespace];

To move the LOB when moving the table, use the following:
ALTER TABLE [table_name] MOVE TABLESPACE [new_tablespace] LOB (lob_item) STORE AS (TABLESPACE [another_new_tablespace]);

To move the PARTITION TABLE to another TABLESPACE, use:
ALTER TABLE [table_name] MOVE PARTITION [partition_name] TABLESPACE [new_tablespace];

Advertisements
« Newer Posts

Create a free website or blog at WordPress.com.