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

3 Comments »

  1. […] to the guys at DBA Sensation, finding the correct commands to move Tables, Indexes and even LOB Segments to a new tablespace was […]

    Pingback by The Angry Geek » Blog Archive » Oracle: Moving Data to a new Tablespace — January 12, 2009 @ 8:02 am

  2. Hi, after reading this remarkable post i am also happy to share my know-how
    here with mates.

    Comment by new york — September 25, 2013 @ 9:41 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: