Script generator for moving objects to another tablaspace

Limitation and aspects
1. Subpartitioning doesn’t support
2. Currently 3 source-target tablespaces pares
3. IOT and lobs, partitioned IOT and lobs are supported
4. Parallelism support
5. Output script can be divided to separate files per owner

DECLARE 
  -- Minimum size of objects for parallel processing
  C_MIN_PARALLEL_SIZE_MB CONSTANT NUMBER := 100;  
  -- Parallel degree for moving and rebuilding
  C_PARALLEL_DEGREE CONSTANT INT := 8;

  TYPE tpt_varchar2 IS TABLE OF VARCHAR2(30);
  -- currently 3 tablespaces supported
  -- 1st old -> 1st new, 2nd old -> 2nd new etc
  l_old_ts tpt_varchar2 := tpt_varchar2('GRIM_DATA1','GRIM_INDX1', '');
  l_new_ts tpt_varchar2 := tpt_varchar2('GRIM_DATA2','GRIM_INDX2', '');


  FUNCTION get_old_ts_list RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || l_old_ts(1) ||''','''|| l_old_ts(2)||''','''||l_old_ts(3) || '''';
  END;

  FUNCTION get_new_ts_list RETURN VARCHAR2 IS
  BEGIN
    RETURN '''' || l_new_ts(1) ||''','''|| l_new_ts(2)||''','''||l_new_ts(3) || '''';
  END;

  FUNCTION get_corresponding_ts(l_old_ts_name VARCHAR2) RETURN VARCHAR2 IS
    i PLS_INTEGER;
  BEGIN
    i := l_old_ts.FIRST;
    WHILE UPPER(l_old_ts(i)) <> UPPER(l_old_ts_name) LOOP
      i := l_old_ts.NEXT(i);
    END LOOP;
    RETURN l_new_ts(i);
  EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, 'Can''t find corresponding ts for ' || l_old_ts_name);
  END get_corresponding_ts;

  FUNCTION get_parallel_clause(l_size_mb NUMBER) RETURN VARCHAR2 IS
  BEGIN
    IF l_size_mb > C_MIN_PARALLEL_SIZE_MB THEN
      RETURN ' PARALLEL ' || C_PARALLEL_DEGREE || ' ';
    END IF;
    RETURN NULL;
  END get_parallel_clause;

  PROCEDURE echo_rebuild_indexes(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.new_line;
    dbms_output.put_line('
PROMPT Rebuild unusable indexes
BEGIN
  FOR rec IN (SELECT * FROM all_ind_partitions WHERE index_owner = ''' || l_owner || ''' AND status = ''UNUSABLE'') LOOP
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_owner || ''.'' || rec.index_name || '' REBUILD PARTITION '' || rec.partition_name || '' PARALLEL ' || C_PARALLEL_DEGREE || ''';
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_owner || ''.'' || rec.index_name || '' NOPARALLEL'';
  END LOOP;

  FOR rec IN (SELECT * FROM dba_indexes WHERE owner = ''' || l_owner || ''' AND status = ''UNUSABLE'') LOOP
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' || rec.index_name || '' REBUILD PARALLEL ' || C_PARALLEL_DEGREE || ''';
    EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.owner || ''.'' || rec.index_name || '' NOPARALLEL'';
  END LOOP;
END;
/');
  END echo_rebuild_indexes;

  PROCEDURE echo_header(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.put_line('SPOOL ' || l_owner || '.log'); 
    dbms_output.put_line('PROMPT Unusable indexes
SELECT owner, index_name, NULL AS part_name FROM all_indexes WHERE owner = ''' || l_owner || ''' AND status = ''UNUSABLE''
UNION ALL
SELECT index_owner, index_name, partition_name AS part_name FROM all_ind_partitions WHERE index_owner = ''' || l_owner || ''' AND status = ''UNUSABLE''
;
PROMPT Check parallel degree greater than 1 
SELECT owner, index_name AS obj_name, DEGREE AS par_degree
FROM all_indexes 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
UNION ALL
SELECT owner, table_name AS obj_name, DEGREE AS par_degree
FROM all_tables 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
;');     
  END echo_header;

  PROCEDURE echo_footer(l_owner VARCHAR2) IS
  BEGIN
    dbms_output.new_line();
    dbms_output.put_line('PROMPT Check parallel degree greater than 1 
SELECT owner, index_name AS obj_name, DEGREE AS par_degree
FROM all_indexes 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
UNION ALL
SELECT owner, table_name AS obj_name, DEGREE AS par_degree
FROM all_tables 
WHERE tablespace_name IN (' || get_old_ts_list || ',' || get_new_ts_list || ')
  AND DEGREE > 1
;

PROMPT Check moving
SELECT owner, table_name AS obj_name, NULL AS part_name
FROM all_tables
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, index_name AS obj_name, NULL AS part_name
FROM all_indexes
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, table_name || ''.'' || column_name AS obj_name, NULL AS part_name 
FROM all_lobs
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name, partition_name 
FROM all_tab_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT index_owner, index_name, partition_name
FROM all_ind_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name || ''.'' || column_name, partition_name
FROM all_lob_partitions
WHERE tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, table_name, ''DEF ATTRIBUTE''
FROM all_part_tables
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT owner, index_name, ''DEF ATTRIBUTE''
FROM all_part_indexes
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT table_owner, table_name || ''.'' || column_name, ''DEF ATTRIBUTE''
FROM all_part_lobs
WHERE def_tablespace_name IN (' || get_old_ts_list || ')
UNION
SELECT USER, segment_name, partition_name
FROM user_segments 
WHERE tablespace_name IN (' || get_old_ts_list || ');

SPOOL OFF');
  dbms_output.put_line(LPAD('-', 40, '-'));  
  END echo_footer;
BEGIN
  FOR rec IN (
      -- not by dba_segments because of delayed segment creation
      SELECT owner 
      FROM dba_tables
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_indexes
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_lobs
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_tab_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT index_owner 
      FROM dba_ind_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_lob_partitions
      WHERE tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_part_tables
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT owner 
      FROM dba_part_indexes
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
      UNION
      SELECT table_owner 
      FROM dba_part_lobs
      WHERE def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
  ) LOOP
    echo_header(rec.owner);
    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned tables');
    FOR obj_rec IN (
        SELECT * 
        FROM dba_part_tables t
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned indexes');
    FOR obj_rec IN (
        SELECT t.* 
        FROM dba_part_indexes t, dba_indexes i
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND i.owner = t.owner
          AND i.index_name = t.index_name
          AND i.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned IOT');
    FOR obj_rec IN (
        SELECT i.table_owner, i.table_name, t.def_tablespace_name 
        FROM dba_part_indexes t, dba_indexes i
        WHERE t.owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND i.owner = t.owner
          AND i.index_name = t.index_name
          AND i.index_type = 'IOT - TOP'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_owner||'"."'||obj_rec.TABLE_NAME 
          ||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Set default attributes for partitioned LOB');
    FOR obj_rec IN (
        SELECT * 
        FROM dba_part_lobs t
        WHERE t.table_owner = rec.owner
          AND t.def_tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_owner||'"."'||obj_rec.TABLE_NAME || '" '
                         ||'MODIFY DEFAULT ATTRIBUTES LOB (' || obj_rec.column_name || ') (TABLESPACE '||get_corresponding_ts(obj_rec.def_tablespace_name)||');');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move non-partitioned tables'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_tables t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)
                           || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move table partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_tab_partitions t, dba_segments s
        WHERE t.table_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.table_owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE PARTITION "'||obj_rec.partition_name || '"'
        || ' TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name) 
        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move IOT table partitions'); 
    FOR obj_rec IN (
        SELECT i.table_owner, i.table_name, t.partition_name, t.tablespace_name, s.bytes/1024/1024 size_mb
        FROM dba_indexes i, dba_ind_partitions t, dba_segments s
        WHERE t.index_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.index_owner = s.owner(+)
          AND t.index_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
          AND i.owner = t.index_owner
          AND i.index_name = t.index_name
          AND i.index_type = 'IOT - TOP'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.table_OWNER||'"."'||obj_rec.TABLE_NAME ||'" MOVE PARTITION "'||obj_rec.partition_name || '"'
        || ' TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)|| ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move indexes'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_indexes t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
          AND t.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME ||'" REBUILD '
      || ' TABLESPACE '||get_corresponding_ts(obj_rec.tablespace_name)||''
      || get_parallel_clause(obj_rec.size_mb) || ';');
      -- return parallel degree back
      dbms_output.put_line('ALTER INDEX "'||obj_rec.OWNER||'"."'||obj_rec.INDEX_NAME||'"' ||' PARALLEL '||obj_rec.DEGREE||';'); 
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move index partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_indexes i, dba_ind_partitions t, dba_segments s
        WHERE t.index_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.index_owner = s.owner(+)
          AND t.index_name = s.segment_name(+)
          AND t.partition_name = s.partition_name(+)
          AND i.owner = t.index_owner
          AND i.index_name = t.index_name
          AND i.index_type NOT IN ('IOT - TOP', 'LOB')
    ) LOOP
      dbms_output.put_line('ALTER INDEX "'||obj_rec.INDEX_OWNER||'"."'||obj_rec.INDEX_NAME||'"'
                        ||' REBUILD PARTITION "'||obj_rec.PARTITION_NAME||'" TABLESPACE '||get_corresponding_ts(obj_rec.tablespace_name)||''
                          || get_parallel_clause(obj_rec.size_mb) || ';');
      -- noparallel not needed for index partitions
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move non-partitioned lobs'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_lobs t, dba_segments s
        WHERE t.owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.partitioned = 'NO'
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.OWNER||'"."'||obj_rec.TABLE_NAME||'"'
                          ||' MOVE LOB ("'||obj_rec.COLUMN_NAME||'") STORE AS (TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name)||')'
                        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    dbms_output.new_line();
    dbms_output.put_line('PROMPT Move lob partitions'); 
    FOR obj_rec IN (
        SELECT t.*, s.bytes/1024/1024 size_mb
        FROM dba_lob_partitions t, dba_segments s
        WHERE t.table_owner = rec.owner
          AND t.tablespace_name IN (l_old_ts(1), l_old_ts(2), l_old_ts(3))
          AND t.table_owner = s.owner(+)
          AND t.table_name = s.segment_name(+)
          AND t.lob_partition_name = s.partition_name(+)
    ) LOOP
      dbms_output.put_line('ALTER TABLE "'||obj_rec.TABLE_OWNER||'"."'||obj_rec.TABLE_NAME||'" MOVE PARTITION "'||obj_rec.PARTITION_NAME||'"'
                        ||' LOB ("'||obj_rec.COLUMN_NAME||'") STORE AS (TABLESPACE '|| get_corresponding_ts(obj_rec.tablespace_name) ||')'
                        || get_parallel_clause(obj_rec.size_mb) || ';');
    END LOOP;

    echo_rebuild_indexes(rec.owner);
    echo_footer(rec.owner);
  END LOOP;
END;