Saturday, April 24, 2010

How to generate move/rebuild script to migrate partitioned tables from one tablespace to other

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:=OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_INDEX_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select table_owner, table_name, partition_name, partition_position from dba_tab_partitions where tablespace_name=oldDataTablespaceName order by table_owner, table_name, partition_position )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.table_owner||'.'||l.table_name||' move partition "'||l.partition_name||'" tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select dip.index_owner, dip.index_name, dip.partition_name from dba_ind_partitions dip, dba_indexes di where di.table_owner=l.table_owner and di.table_name=l.table_name and di.owner=dip.index_owner and di.index_name=dip.index_name and dip.partition_position=l.partition_position)
loop
dbms_output.put_line('alter index '||k.index_owner||'.'||k.index_name||' rebuild partition "'||k.partition_name||'" tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

Here is non-partitioned version:

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:='OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select owner, table_name from dba_tables where tablespace_name=oldDataTablespaceName order by 1,2 )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.owner||'.'||l.table_name||' move tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select di.owner, di.index_name from dba_indexes di where di.owner=l.owner and di.table_name=l.table_name)
loop
dbms_output.put_line('alter index '||k.owner||'.'||k.index_name||' rebuild tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

 

1 comment:

Anonymous said...

Amiable fill someone in on and this post helped me alot in my college assignement. Thanks you as your information.