Split, Merge, Add, Drop, Exchange, Modify, Rename Partition syntax.

If you look for partition related posts in my blog then have a look at,

How to do partition
Partitioning using online re-definition
How to make partitioning in Oracle more Quickly
Example of global partitioned, global non-partitioned and local Indexes
In this post I will write about syntax/example of partitioning related aspects.

1)Split Table Partition Example:

Partition activity_06_2007 of table user_activity split into two partitions at date 15-JUN-2007. Data before date 15-JUN-2007 is stored into partition activity_06_2007a. Data after date 15-JUN-2007 is stored into partition activity_06_2007b.

ALTER TABLE user_activity SPLIT PARTITION activity_06_2007
AT (TO_DATE('15-JUN-2007','DD-MON-YYYY'))
INTO (PARTITION activity_06_2007a, PARTITION activity_06_2007b);

The above example is for split range partition.
The following example is for split partition of list partition.

ALTER TABLE list_country SPLIT PARTITION rest
VALUES ('BANGLADESH', 'PAKISTAN')
INTO (PARTITION asia, partition rest);

2)Merge Table Partition Example:
In part 1) merge range partition as before by,
ALTER TABLE user_activity MERGE PARTITIONS activity_06_2007a, activity_06_2007b
INTO PARTITION activity_06_2007;

In part 1) merge list partition as before by,

ALTER TABLE list_country
MERGE PARTITIONS asia, rest INTO PARTITION rest;

3)Add Table Partition with LOB Example:
Add a new partition into user_activity table and also store lob column into another tablespace data03.
ALTER TABLE user_activity ADD partition prest values less than (MAXVALUE)
LOB (log, description) STORE AS (TABLESPACE data03);

4)Drop Table Partition Example:
ALTER TABLE DROP PARTITION p1;
Drop and update global indexes in one statement.
ALTER TABLE DROP PARTITION p1 UPDATE GLOBAL INDEXES;

5)Exchange Table Partition Example:
Create the same table as of structure(only column and data type is mandatory) of the partitioned table.

And then run command,
ALTER TABLE user_activity
EXCHANGE PARTITION P_JUN_2007 WITH TABLE UA_JUN_2007;

Note that UA_JUN_2007 must be created prior to execute ALTER TABLE ... EXCHANGE command.

6)Modify Table Partitions Example:
Marking local indexes of partition P3 unusable of table user_activity.
ALTER TABLE user_activity MODIFY PARTITION P3
UNUSABLE LOCAL INDEXES;

Rebuilds all the local index partitions that were marked UNUSABLE,
ALTER TABLE user_activity MODIFY PARTITION P3
REBUILD UNUSABLE LOCAL INDEXES;

7)Move Table Partitions Example:
The following statement will move partition P3 of table user_activity to a new tablespace data04;

ALTER TABLE user_activity MOVE PARTITION p3 TABLESPACE data04;

8)Rename Table Partitions Example:
The partition P3 of user_activity table will be renamed as MAR_2008.
ALTER TABLE sales RENAME PARTITION p3 TO MAR_2008;

9)Truncating Table Partitions Example:
Deletes all the data in the MAR_2008 partition of user_activity table and deallocates the freed space,

ALTER TABLE user_activity
TRUNCATE PARTITION mar_2008 DROP STORAGE;

Related Documents

How to do partition
Partitioning using online re-definition
How to make partitioning in Oracle more Quickly
Example of global partitioned, global non-partitioned and local Indexes