Wednesday, March 2, 2011

Exchanging Partitions Example

1) Exchanging a List partition table

The following example will show how to move from one partition table to a new one using an intermediate table.

-- Create our set of tables and populate with some data

CREATE TABLE myschema.my_part_table
(a char(1),
b int
) partition by list(b)
(
partition p_initial values (0),
partition p_part values (1)

);

create index myschema.my_part_table_idx on myschema.my_part_table(b) local;

insert into myschema.my_part_table
select 'A',1
from all_objects;

commit;


create table myschema.my_int_table as select * from myschema.my_part_table where 1=2;
create index myschema.int_my_part_table_idx on myschema.my_int_table(b);


CREATE TABLE myschema.my_new_part_table
(a char(1),
b int
) partition by list(b)
(
partition p_initial values (0),
partition p_part values (1)

);

create index myschema.my_part_table_idx on myschema.my_new_part_table(b) local;

-- Exchange original partition with intermediate table

alter table myschema.my_part_table exchange partition p_part with table myschema.my_int_table
including indexes without validation;

-- Exchange intermediate table with target partition table

alter table myschema.my_new_part_table exchange partition p_part with table myschema.my_int_table
including indexes without validation;


-- Verify the data are in the new partition table
select * from myschema.my_part_table partition (p_part);
select * from myschema.my_new_part_table partition (p_part);
select * from myschema.my_int_table;

No comments:

Post a Comment