6 May 2015

How to move table,indexes,lobs from one tablespace to another?

Moving the user table to another tablespace

 BEGIN
  FOR i IN (
    SELECT * FROM ALL_tables where owner = :owner
      and (tablespace_name is null or tablespace_name != :tbs)
      and temporary != 'Y'
      and partitioned != 'YES'
    ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_tab_partitions
    WHERE table_owner = :owner and tablespace_name != :tbs
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '
      || i.table_name || ' MOVE PARTITION '
      || i.partition_name ||' TABLESPACE '|| :tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_tables where owner = :owner and partitioned = 'YES'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '
      || i.table_name || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_Lobs WHERE owner = :owner
      and (tablespace_name is null or tablespace_name != :tbs)
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs;
  END LOOP;
END;

 Moving indexes to another user tablespace:

 BEGIN
  FOR i IN (
    SELECT * FROM ALL_indexes
    where owner = :owner
      and tablespace_name != :index_tbs
      and index_type != 'IOT - TOP'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '  || i.index_name || ' REBUILD TABLESPACE ' || :index_tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_indexes
    where owner = :owner
      and partitioned = 'YES'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '  || i.index_name
      || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE ' || :index_tbs;
  END LOOP;
END;
BEGIN
  FOR i IN (
    SELECT * FROM ALL_ind_partitions
    where index_owner = :owner
      and tablespace_name != :index_tbs
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' 
      || i.index_name || '  REBUILD PARTITION '
      || i.partition_name || ' TABLESPACE ' || :index_tbs;
  END LOOP;
END;
 
Moving Lob segment:

 BEGIN
  FOR i IN (
    select * from user_lobs where (tablespace_name is null or tablespace_name != :tbs)
    ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE '  || i.table_name || ' MOVE TABLESPACE ' || :tbs
      || ' LOB (' || i.column_name || ')'
      || ' STORE AS ' || i.segment_name || ' (TABLESPACE ' ||  :tbs ||' )';
  END LOOP;

No comments:

Post a Comment