Moving multiple tables and indexes between tablespaces at once

David Lozano Lucas's picture
Submitted by David Lozano Lucas on Fri, 2009-10-23 06:30
articles: 
RDBMS Server

Here I present a simple query to use when we want to move the tables and indexes of several users at once.

To make it one by one:

ALTER TABLE xxxxxx MOVE TABLESPACE TEST;
ALTER INDEX xxxxxx REBUILD TABLESPACE TEST;

To move data from multiple owners. In this example OWNER1 and OWNER2:

select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN ('OWNER1','OWNER2')
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2')

»

Comments

Submitted by rajabaskar on Wed, 2009-11-04 13:48.

Hi David,

Above query has a typo error.

Please use the below query:

select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || TABLESPACE_NAME || ';' from DBA_INDEXES WHERE OWNER IN ('OWNER1','OWNER2');

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || TABLESPACE_NAME || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2');

Regards
Rajabaskar Thangaraj

»

Submitted by jp_vijaykumar on Thu, 2010-01-07 12:45.

Hi David,

I will use a pl/sql procedure for such a task.

This procedure moves/rebuilds the tables/indexes of shema owners SUBBU/SUGGU to
usr_d_01, usr_x_01, usr_d_02 and usr_x_02 tablespaces as per the case condition.

You need to edit the CASE condition as per your database reorg requirements.
Test this procedure in a test db thoroughly, before running in a production db.

 set serverout on size 1000000
 declare
 begin
 for c1 in (select owner,segment_name, segment_type,decode(segment_type,'TABLE','MOVE','REBUILD') operation,
                case when owner='SUBBU' and segment_type='TABLE' then 'usr_d_01' 
                     when owner='SUBBU' and segment_type='INDEX' then 'usr_x_01'  
                     when owner='SUGGU' and segment_type='TABLE' then 'usr_d_02'  
                     when owner='SUGGU' and segment_type='INDEX' then 'usr_x_02' end tablespace_name
               from dba_segments
               where owner in ('SUBBU','SUGGU')
          and segment_type in ('TABLE','INDEX') ) loop
    begin
 
   dbms_output.put_line('alter '||c1.segment_type||'.'||c1.owner||'.'||
                            c1.segment_name||' '||c1.operation||' tablespace '||c1.tablespace_name);

  /* Once you tested and ok with the output of this procedure as per your database requirements
     remove the comments on the below execute immediate statement and run the procedure to perform the task */

  /* execute immediate 'alter '||c1.segment_type||' '||c1.owner||'.'||
                       c1.segment_name||' '||c1.operation||' tablespace '||c1.tablespace_name; */
   exception
   when others then
   dbms_output.put_line(c1.owner||' '||c1.segment_name||' '||c1.segment_type||' '||sqlerrm);
   end;
   end loop;
   end;

Thanks
JP

»