Lesson 6	Manipulating Partitions
Objective	Rename, move, and coalesce Partitions

Manipulating Oracle Partitions

In Oracle 19c, you can rename, move, and coalesce partitions using specific SQL commands.

Renaming a Partition

You can rename a partition in an interval, range, list, or hash-partitioned table using the ALTER TABLE statement:

ALTER TABLE table_name RENAME PARTITION old_partition_name 
TO new_partition_name;

Example:

ALTER TABLE sales RENAME PARTITION sales_q1 TO sales_2024_q1;

Moving a Partition

Moving a partition is useful for changing tablespace storage or reorganizing data.

ALTER TABLE table_name MOVE PARTITION partition_name TABLESPACE new_tablespace;

Example:

ALTER TABLE sales MOVE PARTITION sales_q1 TABLESPACE sales_archive;

For index-organized tables, you must include the UPDATE INDEXES clause to maintain index integrity:

ALTER TABLE sales MOVE PARTITION sales_q1 TABLESPACE sales_archive UPDATE INDEXES;

Coalescing Partitions
The ALTER TABLE ... COALESCE PARTITION command merges adjacent partitions in hash-partitioned tables.
```
ALTER TABLE table_name COALESCE PARTITION;
    
```

Example:

ALTER TABLE sales COALESCE PARTITION;

This command removes an adjacent partition and redistributes its data to other partitions.
Additional Notes:

Renaming partitions is useful for maintenance, e.g., changing naming conventions.
Moving partitions is common in archival strategies, such as moving old data to a different tablespace.
Coalescing partitions applies only to hash partitions, reducing the total number of partitions while keeping data distribution efficient.

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 feature of Oracle since Oracle 8i. Once again, you use the ALTER TABLE command to accomplish the task. 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.
In the following diagram, you can see the code syntax and a real example of moving a partition.
```
ALTER TABLE tablename 
MERGE PARTITIONS partition1, partition2
INTO PARTITION newpartition
```
1. List exactly two adjacent partitions here
2. The new partition newpartition takes on the partition values of the higher partition.
```
ALTER TABLE WHOLESALE_ORDER
MERGE PARTITIONS 
LOW_PART, MED_PART
INTO PARTITION LOWMED_PART
```

Steps To Manipulate Partitions

The code you create to

move a partition to a different tablespace,
merge two partitions into one, and
rename a partition

should look something like this:

ALTER TABLE WHOLESALE_ORDER
MOVE PARTITION LOW_PART TABLESPACE LOW_PART_TS
/
ALTER TABLE WHOLESALE_ORDER
MERGE PARTITIONS
LOW_PART, MED_PART 
INTO PARTITION LOW1_PART
/
ALTER TABLE WHOLESALE_ORDER
RENAME PARTITION LOW1_PART TO LOWEST_PART
/

Moving Partitions Containing LOBs
You can move a LOB partition into a different tablespace. This is useful if the tablespace is no longer large enough to hold the partition. To do so, use the
```
ALTER
TABLE ... MOVE PARTITION 
```
clause.
For example:
```
ALTER TABLE current_table 
MOVE PARTITION partition_name
TABLESPACE destination_table_space
LOB (column_name) 
STORE AS (TABLESPACE current_tablespace);
```

The next lesson covers exchanging a partition with a table.