Oracle Database Scripts

Using List Partitions (Supported in Oracle 9i and above) - Note:149116.1 in Metalink:


The Script:


-- Create table with list partitions
CREATE 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 Partition
ALTER TABLE employees_reg_p TRUNCATE PARTITION ZONE_3 DROP STORAGE;

-- Move Partition
ALTER 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 partition
select * 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;