Online Partition Move

As you know that Oracle partition is a database object used to partition the table’s data into multiple segments which might be stored in different tablespace for the manageability purposes. There are several partition operations that can be performed on the partitions which are called partition maintenance operations and one of them is the “partition move” which means: to move partition from one
tablespace to another tablespace. Partition move could be required in order to manage the data/partition easily and it also helps us to leverage the available storage efficiently. Partitions which are not accessed very frequently and also not hotspots, can be put in the tablespace(s) which reside on a cheaper and slower storage whereas partitions which are hotspots and are also accessed very frequently can be place in the tablespace(s) which reside on a high-end storage.

Prior to 12c, if you plan to move a partition of a table from one tablespace to another tablespace, it would require the partition data to remain unavailable during the execution of this task. It means that if you have huge data in a partition , you might need quite a long time to finish the partition move operation and data would not be available (transactions would need to wait until this move operation finishes.

In oracle 12c and above, you can move a partition from one tablespace to another tablespace online - which means that your partition remains available for operations during the partitions movement is in progress. (To learn about online datafile move/rename feature, click here)

Syntax

ALTER TABLE <table_name> MOVE PARTITION <PARTITION_NAME> TABLESPACE <tablespace_name> UPDATE INDEXES ONLINE;

Example

15:28:51 SQL> CREATE TABLE test
15:28:55   2    ( id       NUMBER(6)
15:28:55   3      , test_date DATE
15:28:55   4    )
15:28:55   5   PARTITION BY RANGE (test_date)
15:28:55   6   ( PARTITION yesterday VALUES LESS THAN (TO_DATE('19-JUN-20yyy'))
15:28:55   7      TABLESPACE mytbs
15:28:55   8   , PARTITION today VALUES LESS THAN (maxvalue)
15:28:55   9      TABLESPACE mytbs
15:28:55  10   );

Table created.

15:28:56 SQL> insert into test values(1,sysdate);

1 row created.

15:29:07 SQL>  insert into test values(1,sysdate-2);

1 row created.

15:29:14 SQL> insert into test select * from test;

2 rows created.

-- Repeat several times to generate huge amount of data to make partition size huge by using following command.
Insert into test select * from test;

15:30:216 SQL> commit;

Commit complete.

15:30:26 SQL> select count(*) from test partition(today);

  COUNT(*)
----------
    262144

15:30:53 SQL> select count(*) from test partition(yesterday);

  COUNT(*)
----------
    262144


15:37:35 SQL> alter table test move partition today tablespace users online ;

While this was in progress, in another window, I did some insertions in partition "today" and I was able to to that, please see bellow image

15:38:05 SQL> select count(*) from test partition(today);

  COUNT(*)
----------
    262163

As above test demonstrates, the facility to move partitions from one tablespace to another tablespace is very handy, specially when downtime can’t be afforded.

One thing to note is that UPDATE INDEX still needs to be specified to update the indexes otherwise indexes would become UNUSABLE. ONLINE clause is also required to be specified to make sure that partition move operation is ONLINE otherwise it would be an OFFLINE operation and data in the partition would not be available for normal operations.