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.

For table partitioned by range with daily partitions i had:

    for k in 0..189 
    loop
        stmt:='ALTER TABLE table_name MOVE PARTITION P'||to_char(to_date('20110419','yyyymmdd')+k,'yyyymmdd')||  ' TABLESPACE NEW_TABLESPACE';
        execute immediate stmt;
        
        stmt:='ALTER TABLE table_name MODIFY PARTITION P'||to_char(to_date('20110419','yyyymmdd')+k,'yyyymmdd') ||' REBUILD UNUSABLE LOCAL INDEXES';
        execute immediate stmt;


    end loop;
and

    for k in 0..389 
    loop
        stmt:='ALTER TABLE Table_name MOVE PARTITION P'||to_char(to_date('20110103','yyyymmdd')+k*7,'yyyyiw')||  ' TABLESPACE NEW TABLESPACE';
        execute immediate stmt;
        
        stmt:='ALTER TABLE table_name  MODIFY PARTITION P'||to_char(to_date('20110103','yyyymmdd')+k*7,'yyyyiw') ||' REBUILD UNUSABLE LOCAL INDEXES';
        execute immediate stmt;


    end loop;


The default tablespace were set by dba as NEW_TABLESPACE.
My partitions look like Pyyyymm, Pyyyymmdd, Pyyyyiw, etc :)
I used the above code to move many tables, but it was an ugly job. I had to find the first partition. If i had a missing partition(in time continuum) the code exits. The job finishes with exception.Bad.

But this was not the single bad part. The worst was that rebuild unusable local indexes clause rebuilds indexes on the same tablespace and you cannot change that. Simply doesn't work to add "Tablespace New_tablespace".

I had to create code to move indexes:

DECLARE 
stmt varchar(1000);
BEGIN
    FOR k IN (select * 
                    from dba_part_indexes  join dba_ind_partitions using (index_name) 
                    where  
                        tablespace_name = 'OLD_TABLESPACE' and 
                        table_name like 'TBL%')
    LOOP
           stmt:='ALTER INDEX '|| k.index_name || ' REBUILD PARTITION ' || k.partition_name || ' TABLESPACE NEW_TABLESPACE';
           EXECUTE IMMEDIATE stmt;
    END LOOP;
END;


This drove me to write better code for moving partitioned tables:




declare
stmt varchar(1000);
begin
    for k in (select table_name, partition_name
                from dba_tab_partitions
                where
                    table_owner = 'my_schema' and
                    table_name = 'TBL%' and
                    tablespace_name = 'OLD_TABLESPACE')
    loop
        stmt:='ALTER TABLE '||k.table_name||' MOVE PARTITION '||k.partition_name||  ' TABLESPACE NEW_TABLESPACE';
        execute immediate stmt;


        for r in (select *
                    from dba_part_indexes
                    where table_name=k.table_name)
        loop
            stmt:='ALTER INDEX '|| r.index_name || ' REBUILD PARTITION ' || k.partition_name || ' TABLESPACE NEW_TABLESPACE';
            execute immediate stmt;
        end loop;


    end loop;
end;

This is more elastic and powerfull and general code than previous.
For non partitioned tables I used:


begin
    for k in (
        select segment_name, round(sum(bytes)/1024/1024), tablespace_name
        from user_segments
        where segment_name not like '%BIN$%' and segment_name not like 'SYS%' and segment_type = 'TABLE' 
        and     tablespace_name = 'OLD_TABLESPACE'
        group by segment_name, tablespace_name
        order by 1 desc)
     loop
        move_table(k.segment_name,0);
    end loop;
end;

where move_table is

CREATE OR REPLACE procedure move_table(tb varchar2, is_mv number) as
    ddlmv varchar2(4000) ;
begin
    execute immediate 'alter table '||tb||' move tablespace NEW_TABLESPACE'; 
    for k in ( select * 
            from dba_indexes 
            where table_name = tb)
    loop
        execute immediate  'alter index '||k.index_name||' rebuild  tablespace NEW_TABLESPACE';
    end loop;
   
    IF is_mv = 1 THEN 
        select dbms_metadata.get_ddl('MATERIALIZED_VIEW',tb,'schema_name')   into ddlmv    from dual;
        execute immediate 'drop materialized view '||tb;
        execute immediate ddlmv;
    END IF;   
end;
/


The second part of the procedure (and second parameter) is for the case the table is a Materialized view because the Materialized view won't refresh after a ddl over the table.


For tables that have LOB columns you can use this simple DDL:
ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name); 
for movig the LOB.


or 


ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));
for moving the table and the LOB.

These are most important cases.