| Lesson 6 | Manipulating Partitions |
| Objective | Rename, move, and coalesce Partitions |
Manipulating Oracle Partitions
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:
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:
For example, to move
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
In the following diagram, you can see the code syntax and a real example of moving a partition.
ALTER TABLE command to accomplish the task.
In the following diagram, you can see the code syntax and a real example of moving a partition.
- List exactly two adjacent partitions here
- The new partition takes on the partition values of the higher partition.
ALTER TABLE tablename MERGE PARTITIONS partition1, partition2 INTO PARTITION newpartition
ALTER TABLE WHOLESALE_ORDER MERGE PARTITIONS LOW_PART, MED_PART INTO PARTITION LOWMED_PART
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.
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
Steps To Manipulate Partitions