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
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;
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 tablenameMOVE 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_LIBRARYMOVE HANK_WILLIAMS_ERA TABLESPACE HANK_TS;
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.
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.