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.

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".

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;