Partitioning Tables  «Prev  Next»
Lesson 6Manipulating Partitions
Objective Rename, move, and coalesce Partitions

Manipulating Partitions in Oracle

Renaming partitions

This lesson covers three changes that you can make to partitions: moving, renaming, and coalescing the partitions.
The capability to move and rename partitions was introduced in Oracle8, when partitioning was initially introduced. You may need to rename a partition because its name no longer describes the data values accurately. As a reminder, here is the syntax for renaming a partition:

ALTER TABLE tablename 

RENAME current_partition_name TO 
   new_partition_name;

For example, to rename the ELVIS_ERA partition in the CD_LIBRARY table to be called the HANK_WILLIAMS_ERA partition, the command looks like this:
ALTER TABLE CD_LIBRARY 

RENAME ELVIS_ERA TO HANK_WILLIAMS_ERA;

Moving partitions


When you move a partition, you are changing its physical storage from one tablespace to another. You may need to do this to enlarge the storage space for a partition.
The syntax for moving a partition is: ALTER TABLE tablename MOVE partition_name new_tablespacename;
For example, to move HANK_WILLIAMS_ERA in the CD_LIBRARY table to a new tablespace called HANK_TS, you would use this command: ALTER TABLE CD_LIBRARY MOVE HANK_WILLIAMS_ERA TABLESPACE HANK_TS;

Merging partitions

Merging, or coalescing, partitions is a new feature of Oracle. Once again, you use the ALTER TABLE command to accomplish the task.
In the following MouseOver, you can see the code syntax and a real example of moving a partition.

  1. List exactly two adjacent partitions here
  2. The new partition takes on the partition values of the higher partition.

Merge two Partitions into One
Merging partitions might be useful when you find that two partitions contain only a few rows because the range in both partitions was originally defined as too narrow a range. The next lesson covers exchanging a partition with a table.

Steps To Manipulate Partitions

Click the Exercise link below to practice moving, coalescing, and renaming partitions.
Steps To Manipulate Partitions