How To Move an Already Existing Partition To a New Tablespace (Doc ID 811123.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7
Information in this document applies to any platform.

Goal

This article suggests the best method to move an already existing partition to a new tablespace,
when the base table is unique and it needs to move its tablespace.

For example, if the base table is using the tablespace "old_data_tbs" and here are all partitions
with their tablespaces:

TABLE_NAME     PARTITION_NAME TABLESPACE_NAME
-------------- -------------- -----------------
CLINICAL_EVENT CLIN_EVENT0801 XPAR_DATA_Q200801
CLINICAL_EVENT CLIN_EVENT0802 XPAR_DATA_Q200802
CLINICAL_EVENT CLIN_EVENT0803 XPAR_DATA_Q200803
CLINICAL_EVENT CLIN_EVENT0804 XPAR_DATA_Q200804
CLINICAL_EVENT CLIN_EVENT0901 XPAR_DATA_Q200901
CLINICAL_EVENT CLIN_EVENT0902 XPAR_DATA_Q200902
CLINICAL_EVENT CLIN_EVENT0903 XPAR_DATA_Q200903
CLINICAL_EVENT CLIN_EVENT0904 XPAR_DATA_Q200904
CLINICAL_EVENT CLIN_EVENT0700 XPAR_DATA_Q200700

and it would like to move the tablespace of the base table to a new tablespace named as "new_data_tbs".

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms