Using List Partitions (Supported in Oracle 9i and above) - Note:149116.1 in Metalink:
The Script:-- Create table with list partitionsCREATE TABLE employees_reg_p
(region VARCHAR2(15),
nm varchar2(100))
partition BY LIST (REGION)
(partition Zone_1 VALUES('R1','R10','R11','R12'),
partition Zone_2 VALUES('R13','R14','R15','R16'),
partition Zone_3 VALUES('R17','R18','R19','R2'),
partition Zone_4 VALUES('R20','R21','R22','R23'),
partition Zone_5 VALUES('R24','R25','R26','R27'),
partition Zone_6 VALUES('R28','R29','R3','R30'),
partition Zone_7 VALUES ('R31','R32','R4','R5'),
partition Zone_8 VALUES ('R6','R7','R8','R9'));
-- Drop a value from a specification of list partition (first must delete rows from partition)-- (Cannot drop the last value in the list of a partition, at least one value must stay!)Delete employees_reg_p WHERE region in ('R32', 'R22');
ALTER TABLE employees_reg_p modify partition zone_4 DROP values ('R22');
ALTER TABLE employees_reg_p modify partition zone_7 DROP values ('R32');
-- Truncate PartitionALTER TABLE employees_reg_p TRUNCATE PARTITION ZONE_3 DROP STORAGE;
-- Move PartitionALTER TABLE employees_reg_p MOVE PARTITION ZONE_3 TABLESPACE users;
-- Insert a row into partition (dont specify the partition)insert into employees_reg_p values('R2','name 1');
-- Select values only from the specified partitionselect * from employees_reg_p partition (zone_3);
-- Change contents of partition 'zone_3' with another table called: 'tst'create table tst as select * from employees_reg_p;
alter table employees_reg_p EXCHANGE PARTITION ZONE_3 WITH TABLE tst WITHOUT VALIDATION;
