Oracle Database 12c -- How to Move Objects from One Tablespace to Another

Problem

You want to move (migrate) all schema objects from one tablespace to another tablespace, with an option of making the new target tablespace as the default tablespace for the objects.

 

Solution

Preparation:

• Create the new target tablespace of appropriate size
• Assign the new target tablespace as the new default tablespace for user
• Grant quota on the new target tablespace to the user

 

Implementation:

1) Identify what segment types exist in the schema:

select  distinct segment_type from user_segments order by 1;

SEGMENT_TYPE    
------------------
INDEX            
INDEX PARTITION  
INDEX SUBPARTITION
LOB PARTITION    
TABLE            
TABLE PARTITION  
TABLE SUBPARTITION
 7 rows selected  

2) Use ALTER statements to move/modify objects:

To move table and table partitions, and subpartitions use ALTER TABLE statements:

select 'alter table '||table_name||' move tablespace <target_tablespace>;' from user_tables where tablespace_name='<source_tablespace>';

select 'alter table '||table_name||' move partition '||partition_name||' tablespace <target_tablespace>;' from user_tab_partitions where tablespace_name='<source_tablespace>' and composite='NO';

select 'alter table '||table_name||' move subpartition '||subpartition_name||' tablespace <target_tablespace>;' from user_tab_subpartitions where tablespace_name='<source_tablespace>';

select 'alter table '||table_name||' modify default attributes tablespace <target_tablespace>;' from user_part_tables where def_tablespace_name='<source_tablespace>';

To move LOBs and LOB partitions:

select 'alter table '||table_name||' move lob('||column_name||') store as (tablespace <target_tablespace>);' from user_lobs where tablespace_name='<source_tablespace>' and partitioned='NO';

select 'alter table '||table_name||' move partition '||partition_name||' lob('||column_name||') store as (tablespace <target_tablespace>);' from user_lob_partitions where tablespace_name='<source_tablespace>';

To move index and index partitions and index subpartitions use ALTER INDEX statements:

select 'alter index '||index_name||' rebuild online tablespace <target_tablespace>;' from user_indexes where tablespace_name = '<source_tablespace>';


select 'alter index '||index_name||' rebuild partition '||partition_name||' online tablespace <target_tablespace>;' from user_ind_partitions where tablespace_name='<source_tablespace>' and composite='NO';

select 'alter index '||index_name||' rebuild subpartition '||subpartition_name||' online tablespace <target_tablespace>;' from user_ind_subpartitions where tablespace_name='<source_tablespace>';

select 'alter index '||index_name||' modify default attributes tablespace <target_tablespace>;' from user_part_indexes where def_tablespace_name='<source_tablespace>';



3) You may also use below combined script to move the segments all at once:


begin
  for c in (
    select 'alter table '||table_name||' move tablespace <target_tablespace>' txt from user_tables where tablespace_name='<source_tablespace>'
    union all
    select 'alter table '||table_name||' move partition '||partition_name||' tablespace <target_tablespace>' from user_tab_partitions where tablespace_name='<source_tablespace>' and composite='NO'
    union all
    select 'alter table '||table_name||' move subpartition '||subpartition_name||' tablespace <target_tablespace>' from user_tab_subpartitions where tablespace_name='<source_tablespace>'
    union all
    select 'alter table '||table_name||' modify default attributes tablespace <target_tablespace>' from user_part_tables where def_tablespace_name='<source_tablespace>'
    union all
    select 'alter table '||table_name||' move lob('||column_name||') store as (tablespace <target_tablespace>)' from user_lobs where tablespace_name='<source_tablespace>' and partitioned='NO'
    union all
    select 'alter table '||table_name||' move partition '||partition_name||' lob('||column_name||') store as (tablespace <target_tablespace>)' from user_lob_partitions where tablespace_name='<source_tablespace>'
    union all
    select 'alter index '||index_name||' rebuild online tablespace <target_tablespace>' from user_indexes where tablespace_name = '<source_tablespace>'
    union all
    select 'alter index '||index_name||' rebuild partition '||partition_name||' online tablespace <target_tablespace>' from user_ind_partitions where tablespace_name='<source_tablespace>' and composite='NO'
    union all
    select 'alter index '||index_name||' rebuild subpartition '||subpartition_name||' online tablespace <target_tablespace>' from user_ind_subpartitions where tablespace_name='<source_tablespace>'
    union all
    select 'alter index '||index_name||' modify default attributes tablespace <target_tablespace>' from user_part_indexes where def_tablespace_name='<source_tablespace>'
  ) loop
    execute immediate c.txt;
  end loop;
end;