Moving data from one Tablespace to another (including partitions/locally managed indexes)

Scenario: A tablespace is holding 200gb but only 50gb is used. To be able to reclaim the unused 150gb back to the O/S, the tablespace would need to be dropped. Before dropping the tablespace, the existing 50gb’s worth of data and indexes needs to be moved so that it is not lost

A New tablespace is created called TS_NEWPLACE

To move individual tables and associated indexes use the following command:

ALTER TABLE SCHEMA.TABLE1 MOVE TABLESPACE TS_NEWPLACE;

To Move the associated index for TABLE1 use the following command. A rebuild will more or less recreate the index in its new destination:

ALTER INDEX SCHEMA.INDEX1_TABLE1 REBUILD TABLESPACE TS_NEWPLACE;

To move a partitioned table with locally managed indexes (i.e partitioned indexes per table partition), use the following commands:

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_01 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_01 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_02 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_02 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_03 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_03 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_04 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_04 TABLESPACE TS_NEWPLACE NOLOGGING;

ALTER TABLE SCHEMA.SEARCHES MOVE PARTITION SEARCHES_2006_05 TABLESPACE TS_NEWPLACE NOLOGGING;
ALTER INDEX SCHEMA.I_SEARCHES_SEARCH_DATE REBUILD PARTITION SEARCHES_2006_05 TABLESPACE TS_NEWPLACE NOLOGGING;

The above allows individual partitions to be moved regardless of the other partitions. NOLOGGING is used to minimize the amount of REDO which is generated.

To change an index from a Globally managed index to a partitioned index use the following commands:

DROP INDEX SCHEMA.INDX_SEARCH_DATE;

Then recreate the index as a locally managed index:

CREATE INDEX SCHEMA.INDX_SEARCH_DATE ON SCHEMA.SEARCHES (search_date) TABLESPACE TS_NEWPLACE LOCAL;

Advertisements

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

%d bloggers like this: