Managing Oracle Partitions

Operations allowed on partitions are

• ADD
• DROP
• TRUNCATE
• COALESCE
• MODIFY
• RENAME
• MOVE
• EXCHANGE
• SPLIT
• MERGE
• REBUILD

Oracle provides a comprehensive set of SQL commands for managing partitioning tables, which includes commands for adding new partitions, dropping, truncating, coalescing, modifying, renaming, moving, exchanging between tables, splitting, merging and compressing partitions.

Creating Partitioned Table:
Creating Oracle partitions is very similar to creating a table or index. We must use the CREATE TABLE statement with the PARTITION CLAUSE.

The first step to create a partitioned table would be to identify the column(s) to partition on and the range of values which go to each partition. Then we can determine the tablespaces where each partition should go.

Here is a script to create a simple partitioned table:

CREATE TABLE table-name
(...col-list...) ....
PARTITION BY ... (col-name)
(
 PARTITION part-name ... [TABLESPACE tbs-name],
 PARTITION part-name ... [TABLESPACE tbs-name],
 ... ... ... ...
 PARTITION part-name ... [TABLESPACE tbs-name]
);

Adding Oracle Partitions:
We can use the ALTER TABLE ADD PARTITION statement to add a new partition to the "high" end. When the partition bound on the highest partition is anything other than MAXVALUE, we can add a partition using the ALTER TABLE ADD PARTITION statement.

SQL> ALTER TABLE ttt ADD PARTITION ppp VALUES ( 'value-list' ) TABLESPACE tbs;

If we wish to add a partition at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE, we should use SPLIT PARTITION statement.

When there are local indexes defined on the table and we issue the ALTER TABLE ... ADD PARTITION statement, a matching partition is also added to each local index. Since Oracle assigns names and default physical storage attributes to the new index partitions, we may wish to rename or alter them after the ADD operation is complete.

Moving Oracle Partitions:
We can use the MOVE PARTITION clause to move a partition. For example, we can move the most active partition to a tablespace that resides on its own disk (in order to balance I/O).

SQL> ALTER TABLE ttt MOVE PARTITION ppp TABLESPACE tbs [NOLOGGING];

This statement always drops the partitions old segment and creates a new segment, even if we don't specify a new tablespace. When the partition we are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. We must rebuild these index partitions after issuing MOVE PARTITION.

Modifying Oracle Partitions:
We can use the MODIFY PARTITION clause to add values to a partition or drop values from a partition.

SQL> ALTER TABLE ttt MODIFY PARTITION ppp ADD/DROP VALUES ('value-list') TABLESPACE tbs;

SQL> ALTER TABLE ttt SET SUBPARTITION TEMPLATE (... ... ..);

Renaming Oracle partitions:
We can use the RENAME PARTITION clause to rename a partition.

SQL> ALTER TABLE ttt RENAME PARTITION ppp TO ppp_new;

Dropping Oracle Partitions:
We can use the ALTER TABLE DROP PARTITION statement to drop Oracle partitions.

SQL> ALTER TABLE ttt DROP PARTITION ppp [UPDATE GLOBAL INDEXES];

If there are local indexes defined for the table, ALTER TABLE DROP PARTITION also drops the matching partition from each local index.

We cannot explicitly drop a partition for a local index. Instead, local index partitions are dropped only when we drop a partition from the underlying table.

If the partition contains data and global indexes, and we leave the global indexes in place during the ALTER TABLE DROP PARTITION statement which marks all global index partitions unusable, we must rebuild them afterwards.

Truncating Partitioned Tables:
We can use the ALTER TABLE TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space.

SQL> ALTER TABLE ttt TRUNCATE PARTITION ppp [DROP STORAGE];

If there are local indexes defined for this table, ALTER TABLE TRUNCATE PARTITION also truncates the matching partition from each local index.

Coalescing Oracle Partitions:
SQL> ALTER TABLE ttt COALESCE PARTITION ppp;

Splitting Oracle Partitions:
We can split a table partition by issuing the ALTER TABLE SPLIT PARTITION statement.

SQL> ALTER TABLE ttt SPLIT PARTITION ppp AT(value) INTO (PARTITION p1 [TABLESPACE tbs-name], PARTITION p2 [TABLESPACE tbs-name]) [UPDATE GLOBAL INDEXES];

If there are local indexes defined on the table, this statement also splits the matching partition in each local index. Because Oracle assigns system-generated names and default storage attributes to the new index partitions, we may wish to rename or alter these index partitions after splitting them.

If the partition we are splitting contains data, the ALTER TABLE SPLIT PARTITION statement marks the matching partitions (there are two) in each local index, as well as all global index partitions, as unusable. We must rebuild these index partitions after issuing the ALTER TABLE SPLIT PARTITION statement.

Exchanging Table Partitions:
We can convert a partition into a non-partitioned table, and a table into a partition of a partitioned table by exchanging their data and index segments.

SQL> ALTER TABLE ttt EXCHANGE PARTITION ppp WITH TABLE newtbl [INDEXES] [VALIDATION] [EXCEPTIONS INTO];

Exchanging table partitions is most useful when we have an application using non-partitioned tables which we want to convert to partitions of a partitioned table.

Merging Oracle Partitions:
Partition-level export and import provide a way to merge Oracle partitions in the same table.

SQL> ALTER TABLE ttt MERGE PARTITIONS p1, p2, p3,... INTO PARTITION p1 [TABLESPACE tbs-name];

Note: Merged partitions should be one of partitions in merge list (like p1).

A DBA can use partition-level import to merge a table partition into the next highest partition on the same table. To merge partitions, do an export of the partition you would like to merge, delete the partition and do an import.

Rebuild Oracle Partition Indexes:
We can rebuild indexes on Oracle partitions by using REBUILD PARTITION commands.

SQL> ALTER TABLE ttt REBUILD PARTITION ppp TABLESPACE tbs-name;

SQL> ALTER INDEX index_name REBUILD PARTITION ind_part_name ONLINE;

NOTE: All above operations can be performed on subpartitions also, by replacing PARTITION keyword bySUBPARTITION keyword.

Analyzing Partitions/Subpartitions:
ANALYZE TABLE table-name PARTITION (partition-name) COMPUTE|ESTIMATE|DELETE STATISTICS;
ANALYZE TABLE table-name SUBPARTITION (subpartition-name) COMPUTE|ESTIMATE|DELETE STATISTICS;

Source: 

Sachin's DBA Blog