Just few days ago I've got a task to move all data from a tablespace to another - disk replacement.
My thought was: "simple job, excepting partitioned tables."
No, you do not need to recreate the tables(with all corresponding indexes) and make insert into new_table select * from old_table. I have 700 tables, few of them near 10GB, but many around 1GB.
First code I managed looked like this:
declare
stmt varchar(1000);
begin
for k in 0..189
loop
stmt:='ALTER TABLE table_name MOVE PARTITION P'||to_char(add_months(to_date('201102','yyyymm'),k),'yyyymm')|| ' TABLESPACE NEW_TABLESPACE';
execute immediate stmt;
stmt:='ALTER TABLE table_name MODIFY PARTITION P'||to_char(add_months(to_date('201102','yyyymm'),k),'yyyymm') ||' REBUILD UNUSABLE LOCAL INDEXES TABLESPACE NEW_TABLESPACE';
execute immediate stmt;
end loop;
end;
for tables partitioned by range in a monthly manner.