MOVE LESS THAN 5 GB TABLES AND INDEXES INTO OTHER TABLESPACE AND REBUILD INDEXES

·      Note:- DO NOT RUN ON PRODUCTION WITHOUT TEST THIS ON TEST ENVIRONMENT.

           --> Accept source tablespace name parameter

·        --> Loop on all tables less than 5GB  inside the tablespace.

·        --> Move Each table to target tablespace and rebuild its indexes

·        --> Indexes reside on same source tablespace will be moved to target tablespace.

·        -->  Reset of indexes reside in different tablespaces will be rebuild their in place.

CREATE OR REPLACE PROCEDURE move_smaller_than_5g (v_source_ts in varchar,v_target_ts in varchar)
AS
BEGIN
FOR i
IN (SELECT OWNER ,SEGMENT_NAME
FROM dba_segments
where TABLESPACE_NAME = v_source_ts
and SEGMENT_TYPE='TABLE'
AND BYTES<5368709120
GROUP BY OWNER,SEGMENT_NAME,SEGMENT_TYPE)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE '|| i.owner|| '.'|| i.SEGMENT_NAME|| '  MOVE TABLESPACE '|| v_target_ts;
--DBMS_OUTPUT.put_line ('ALTER TABLE '|| i.owner|| '.'|| i.SEGMENT_NAME|| '  MOVE TABLESPACE '|| v_target_ts);
for ii in(
select A.owner,A.index_name,A.index_type,A.PARTITIONED,A.status,B.CREATED,A.LAST_ANALYZED,A.TABLESPACE_NAME from dba_indexes A ,DBA_OBJECTS B, dba_part_indexes C where A.table_name = i.SEGMENT_NAME
AND A.OWNER=B.OWNER
AND A.INDEX_NAME=B.OBJECT_NAME
Group by a.owner,a.index_name,A.index_type,A.PARTITIONED,A.status,B.CREATED,A.LAST_ANALYZED,A.TABLESPACE_NAME
order by 1,2)
loop
IF II.TABLESPACE_NAME =v_source_ts
THEN
EXECUTE IMMEDIATE 'ALTER INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12 TABLESPACE '|| v_target_ts;
EXECUTE IMMEDIATE 'ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ';
--DBMS_OUTPUT.put_line ('ALTER INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12 TABLESPACE '|| v_target_ts);
--DBMS_OUTPUT.put_line ('ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ');
ELSE
EXECUTE IMMEDIATE 'ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12';
EXECUTE IMMEDIATE 'ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ';
--DBMS_OUTPUT.put_line ('ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  rebuild NOLOGGING PARALLEL 12');
--DBMS_OUTPUT.put_line ('ALTER  INDEX '|| ii.owner|| '.'|| ii.index_NAME|| '  LOGGING NOPARALLEL ');
END IF;
END LOOP;
END LOOP;
END;
/