Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts

Thursday, May 19, 2011

Oracle - move partitioned tables to another tablespace

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.