Partition Maintenance Operations

Partition Maintenance Operations

Partition Management Commands
Moving Partitions
Use the ALTER TABLE command with the MOVE PARTITION clause to move a partition to a different tablespace. If the partition contains data, all global and local index partitions are marked as unusable. Either rebuild them or drop and re-create the indexes after moving a table partition.
Adding Partitions
Use the ALTER TABLE command with the ADD PARTITION clause to add a new partition to the high end. If the partition bound on the last partition in MAXVALUE or you want to add a partition at the start or middle of a table use the SPLIT PARTITION clause of the ALTER TABLE command.
If there is a local index defined on the table you are adding a partition to, Oracle will automatically add a matching index partition. Can only add a new highest partition to a global index and must use the SPLIT INDEX clause.
To add a partition to a hash index use either:
ALTER TABLE employee ADD PARTITION;
or
ALTER TABLE employee ADD PARTITION p3 TABLESPACE t3;
Local indexes need to be rebuilt when adding hash partitions.
Dropping Partitions
Use the ALTER TABLE command with the DROP PARTITION clause to drop a table partition. This command drops the partition and the data in the partition.
Any local indexes defined on the table will have their corresponding partitions dropped. You can not explicitly drop a local index partition.
You can not drop the only partition in a table.
If a global index partition is empty it can be dropped using the ALTER INDEX...DROP PARTITION command.
Dropping a global index partition with data causes the next highest partition to be marked as unusable. Must perform a rebuild on the higher partition after dropping the lower one.
Can not drop the highest partition of a global index.
Two methods for dropping partitions that contain data and have a global index defined on them:
1. Don’t drop the global indexes. Issue ALTER TABLE...DROP PARTITION statement with actually deleting the data from the partition.
· Global indexes and non-partitioned are marked as unusable and you need to rebuild them afterwards.
· Can only REBUILD a partitioned index “one partition at a time”. Issue ALTER INDEX...REBUILD COMMAND for each index partition to rebuild.
· Use this method for large tables where the partition that is being dropped comprises a large portion of the total data in the table.
2. Delete the rows from the partition before dropping the partition. Use on small tables or partitions with little data.
· The delete command will generate redo and fire any related triggers. Use ALTER TABLE...MODIFY PARTITION...NOLOGGING to turn off logging for the individual partition before executing the delete command and it will reduce the amount of time it takes to perform the delete.

Two methods for dropping partitions that contain data and have referential integrity constraints defined on them:
1. Disable the integrity constraints, drop the partition and then re-enable the integrity constraint(s).
Use this method for large tables where the partition that is being dropped comprises a large portion of the total data in the table.
2. Delete the rows and then drop the partition. The delete command will generate redo and fire any related triggers. Use on small tables or partitions with little data.

Coalescing Partitions
Use the ALTER TABLE...COALESCE PARTITION command to reduce the number of partitions created by the HASH method and redistribute the remaining data among the remaining partitions. Removes a single partition and Oracle selects which one. Any affected local indexes must be rebuilt.
Truncating Partitions
The ALTER TABLE...TRUNCATE PARTITION command will remove all rows from table partition. This command will also truncate any associate local index partitions.
Can not TRUNCATE an index partition directly.
If a partition contains data and global indexes, it can be truncated in one of 2 ways:
Use the ALTER TABLE...TRUNCATE PARTITION command leaving the global indexes in place. Marks any global and non-partitioned indexes as unusable. Must rebuild each partition of the global partitioned index, one at a time. Use when the table is large or has the majority of data in the table.
Delete the rows in the partition before truncating. Use when the table is small or the partition in question has a small % of total rows in table.
Two methods for truncating partitions that contain data and have referential integrity constraints defined on them:
1. Disable the integrity constraints, truncate the partition and then re-enable the integrity constraint(s).
Use this method for large tables where the partition that is being truncated comprises a large portion of the total data in the table.
2. Delete the rows and then truncate the partition. The delete command will generate redo and fire any related triggers. Use on small tables or partitions with little data.

Splitting Partitions
Use the ALTER TABLE...SPLIT PARTITION command to split a partition. When you split a table partition, new local index partitions are created if a local index exists on the table.
Oracle gives system generated names and default storage parameters to partitions created by splitting. These names and storage values can be changed.
If there is data in the partition being split and local and global indexes are marked as unusable after a split. You need to rebuild each partition individually, after the split.
Local index partitions can not be explicitly split, only indirectly as the result of a table partition split.
Merging Partitions
Two adjacent range or composite partitions can be merged into a single partition using the ALTER TABLE...MERGE PARTITIONS ...INTO PARTITION command. The new partition gets the higher upper bound from the two merged partitions. Data is physically moved.
Exchanging Partitions
Use this feature to convert a partitioned table into a non-partitioned table and vice-versa. Data and index segments are exchanged between the two tables.
In an exchange, no data is actually moved. Just the data dictionary is updated. It is a 2-way exchange and the user must have ALTER TABLE privileges on both tables to perform the operation.

Partition Management Restrictions
ALTER TABLE....
Can not add LONG or LONG RAW column types.
Can not add a column with a user-specified default.
Can not alter the datatype of the partition column.
Can use this command to change logical table attributes, which include enabling constraints.
Move Partition Restrictions
For a HASH partition can only specify the TABLESPACE attribute in the MOVE clause.
Can not move the partition if there is a composite partition, must move each individual sub-partitions using the MOVE_SUBPARTITION_CLAUSE.
Add Range-Partition Restrictions
Max partitions = (64K–1)
Can not add a partition if high partition is bound with the MAXVALUE. Use split partition command to add partition at beginning or middle of table.
Split Partition Restrictions
Can not use this command for a HASH partitioned table.
Merge Partition Restrictions
Can not use on an index-organized table.
Can not use on a HASH partitioned table.
Hash Partition Restrictions
ALTER TABLE...SPLIT PARTITION
ALTER TABLE...DROP PARTITION
ALTER TABLE...MERGE PARTITIONS

Partition Management Privileges
ALTER TABLE....
In your own schema
ALTER table privilege
ALTER ANY INDEX
...DROP or TRUNCATE PARTITION
DROP ANY TABLE... if not the table owner.
...ADD, MODIFY, MOVE, SPLIT PARTITION
1. Need space quota in applicable tablespaces.

Related Data Dictionary Views
ALL_TAB_PARTITIONS
ALL_TAB_SUBPARTITIONS
ALL_TABLES
ALL_IND_PARTITIONS
ALL_IND_PARTITIONS
ALL_INDEXES
ALL_LOB_PARTITIONS
ALL_LOB_SUBPARTITIONS
ALL_PART_COL_STATISTICS
ALL_PART_HISTOGRAMS
ALL_PART_INDEXES
ALL_PART_KEY_COLUMNS
ALL_PART_LOBS
ALL_PART_TABLES