In order to move a partitioned table to a different tablespace, each partitioned has to be moved. This can be accomplished two ways: manually moving each partitioned or writing a procedure that will loop through each partition and move it. I will discuss how to write a procedure to take care of this task. First, a partitioned table needs to be created and have some values inserted into it. This table is created in the HR schema and partitioned by range:

CREATE TABLE hr.partitioned_table
(
  customer_id       NUMBER(5),
  item_id           NUMBER(5),
  transaction_date  DATE
)
PARTITION BY RANGE(transaction_date)
(
  PARTITION part_1 VALUES LESS THAN (TO_DATE('20120101', 'YYYYMMDD')),
  PARTITION part_2 VALUES LESS THAN (TO_DATE('20120201', 'YYYYMMDD')),
  PARTITION part_3 VALUES LESS THAN (TO_DATE('20120301', 'YYYYMMDD')),
  PARTITION part_4 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO hr.partitioned_table VALUES 
  (1, 10, TO_DATE('20120101', 'YYYYMMDD'));
INSERT INTO hr.partitioned_table VALUES 
  (1, 10, TO_DATE('20120201', 'YYYYMMDD'));
INSERT INTO hr.partitioned_table VALUES 
  (1, 10, TO_DATE('20120301', 'YYYYMMDD'));
INSERT INTO hr.partitioned_table VALUES 
(1, 10, TO_DATE('20120401', 'YYYYMMDD'));

This table is now created in the default tablespace and has 4 partitions. To see which tablespace the table was created in, run the following query:

SELECT DISTINCT tablespace_name
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONED_TABLE'
 AND table_owner  = 'HR';

In order to move this table to another tablespace, each partition has to be moved. This can be done manually by writing a statement like the following for each partition where (table_name) is the name of the partitioned table, (partition_name) is the name of the partition, and (tablespace_name) is the destination tablespace:

ALTER TABLE (table_name)
 MOVE PARTITION (partition_name)
 TABLESPACE (tablespace_name);

Instead of moving each partition manually, a procedure can be written that will execute the above ALTER TABLE statement for each partition.

MOVE_PARTITION Procedure

The MOVE_PARTITION procedure will move each partition one by one. This eliminates the manual aspect of moving a partition table by using ALTER TABLE…MOVE PARTITION…TABLESPACE… for each partition. Below is the MOVE_PARTITION procedure which uses a FOR loop to loop through each partition of the partitioned table:

CREATE OR REPLACE PROCEDURE move_partition(
  pSchema                VARCHAR2,
  pTableName             VARCHAR2,
  pDestinationTablespace VARCHAR2)
IS
/*--------------------------------------------------------------------------
--Creates a procedure that will move a partitioned table to a different     
--Tablespace.
----------------------------------------------------------------------------
--pSchema = Scheme the partitioned table is is
--pTableName = the name of the partitioned table
--pDestinationTablespace = The tablespace the partitioned table is going to 
--                         be moved to.                                   */
BEGIN

  -- FOR loop that loops through each partition of pTablename               
  FOR i IN (SELECT partition_name 
              FROM all_tab_partitions 
             WHERE table_name = pTableName)
  LOOP

  -- Moves the current partition to the desired tablespace                
  EXECUTE IMMEDIATE 'ALTER TABLE ' || pSchema || '.' || pTableName || 
                    ' MOVE PARTITION ' || i.partition_name || 
                    ' TABLESPACE ' || pDestinationTablespace;
  END LOOP;
END;
/

This procedure can be used to move each partition of a partitioned table to a new tablespace. Currently, the partitioned table that was created prior is in the default tablespace with 4 partitions:

SELECT tablespace_name, COUNT(*)
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONED_TABLE'
   AND table_owner = 'HR'
 GROUP BY tablespace_name;

TABLESPACE_NAME                  COUNT(*)
------------------------------ ----------
USERS                                   4

The schema the table is in, the table name, and the tablespace that the partitioned table is going to be moved to can be put as parameters for the MOVE_PARTITION procedure:

EXECUTE move_partition('HR', 'PARTITIONED_TABLE', 'EXAMPLE');

Once the procedure completes, check to make sure that the partitions have been moved:

SELECT tablespace_name, COUNT(*)
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONED_TABLE'
   AND table_owner = 'HR'
 GROUP BY tablespace_name;

TABLESPACE_NAME                  COUNT(*)
------------------------------ ----------
EXAMPLE                                 4

Comment