Partition Management

Operations  in Partition Table

Partitions Management:-Below are the operations that can do on Partition Table.
1) ADD Partition
2) DROP Partition
3) TRUNCATE Partition
4) MODIFY Partition
5) RENAME Partition
6) MOVE Partition
7) EXCHANGE Partition
8) SPLIT Partition
9) MERGE Partition
10) REBUILD Partition

1) ADD Partitions:-We can use the "ALTER TABLE ADD PARTITION" statement to add a new partition.
Example :-
1) Range Partition
SQL> ALTER TABLE orders ADD PARTITION p_2000 VALUES LESS THAN ( to_date('01-JAN-2001','DD-MON-YYYY') ) ;
SQL> ALTER TABLE orders ADD PARTITION p_2001 VALUES LESS THAN ( to_date('01-JAN-2002','DD-MON-YYYY') ) ;
2) List Partition
SQL> ALTER TABLE sales ADD PARTITION p_cm VALUES ('UK,'PJ') ;
SQL> ALTER TABLE sales ADD PARTITION p_cn VALUES ('KA','TN') ;

Note:-If we want 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 then
      we should use SPLIT PARTITION statement.

2) DROP Partition:-DROP Partition is used to drop the partition
Example :-
1) Range Partition
SQL> ALTER TABLE orders DROP PARTITION p_2000;
SQL> ALTER TABLE orders DROP PARTITION p_2001;
2) List Partition
SQL> ALTER TABLE sales DROP PARTITION p_cm ;
SQL> ALTER TABLE sales DROP PARTITION p_cn ;

3) TRUNCATE Partition:-
Example :-
1) Range Partition
SQL> ALTER TABLE orders TRUNCATE PARTITION p_2000;
SQL> ALTER TABLE orders TRUNCATE PARTITION p_2001;
2) List Partition
SQL> ALTER TABLE sales TRUNCATE PARTITION p_cm ;
SQL> ALTER TABLE sales TRUNCATE PARTITION p_cn ;

4) MODIFY Partition:-We can use the MODIFY PARTITION clause to add values to a partition or drop values from a partition.
Example
SQL> ALTER TABLE sales MODIFY PARTITION p_cm ADD/DROP VALUES ('value-list') TABLESPACE tbs;
SQL> ALTER TABLE orders SET SUBPARTITION TEMPLATE (... ... ..);
 
5) RENAME Partition:-
Example :-
1) Range Partition
SQL> ALTER TABLE orders RENAME PARTITION p_2000 TO p_2000_new;
SQL> ALTER TABLE orders RENAME PARTITION p_2001 TO p_2001_new;
2) List Partition
SQL> ALTER TABLE sales RENAME PARTITION p_cm TO p_cm_new;
SQL> ALTER TABLE sales RENAME PARTITION p_cn To p_cn_new;
 
6) MOVE Partition:-We can use the MOVE PARTITION clause to move a partition.

Example:-
a) Range PartitionSQL> ALTER TABLE orders MOVE PARTITION p_2000 TABLESPACE USERS ;
SQL> ALTER TABLE orders MOVE PARTITION p_2001 TABLESPACE USERS ;
 
b) List Partition
SQL> ALTER TABLE sales MOVE PARTITION p_cm TABLESPACE USERS ;
SQL> ALTER TABLE sales MOVE PARTITION p_cn TABLESPACE USERS ;
 
7) EXCHANGE Partition :-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.
Example
SQL> ALTER TABLE ttt EXCHANGE PARTITION ppp WITH TABLE newtbl ;

Note:-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.
 
8) SPLIT Partition :-We can split a table partition by issuing the ALTER TABLE SPLIT PARTITION statement.
Example
SQL> ALTER TABLE ttt SPLIT PARTITION ppp AT(value) INTO (PARTITION p1, PARTITION p2 ) [UPDATE GLOBAL INDEXES];
 
9) MERGE Partition :-Partition-level export and import provide a way to merge Oracle partitions in the same table.
Example
SQL> ALTER TABLE ttt MERGE PARTITIONS p1, p2, p3,... INTO PARTITION p1 ;

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

10) REBUILD Partition :-We can rebuild indexes on Oracle partitions by using REBUILD PARTITION commands.
Example
SQL> ALTER TABLE orders REBUILD PARTITION p_2000 TABLESPACE USERS;
SQL> ALTER INDEX index_name REBUILD PARTITION ind_part_name ONLINE;