Shrink an oracle tablespace

tablespace_shrink.sql

/*
It is not possible to shrink a tablespace that's being used, in order to reclaim the unused space you can use this script (remember to change the value of OLD_TBSP variable)

The script essentially does the following:
- create a new tablespace appending "_TMP" at the end, it's going to use a datafile with the same name, adding (or incrementing) an id;
- move all the object from the old tablespace to the new one;
  (this means that on the filesystem there should be enough space for the new datafile, consider also the extra space for the index rebuild!)
- prints the commands to execute in order to drop the old tablespace and rename the new.

At the end it is possible that the space on the filesystem is not freed, follow the instructions here: http://serverfault.com/questions/501963/how-to-recover-free-space-on-deleted-files-without-restarting-the-referencing-pr
*/

DECLARE
  EXEC_IMM     VARCHAR(255) := '';
  OLD_TBSP     VARCHAR(80) := 'TABLE_SPACE_TO_BE_SHRINKED';
  NEW_TBSP     VARCHAR(80);
  TBSP_FILE    VARCHAR(250);
  TBSP_FILE_ID NUMBER;
BEGIN
  NEW_TBSP := OLD_TBSP || '_TMP';
  SELECT
    FILE_NAME,
    nvl(regexp_replace(TBSP_FILE, '^(.*[^0-9])(_([0-9]+))*(\.[a-z]+)$', '\3'), 0) + 1
  INTO TBSP_FILE, TBSP_FILE_ID
  FROM DBA_DATA_FILES
  WHERE TABLESPACE_NAME = OLD_TBSP;
  SELECT regexp_replace(TBSP_FILE, '^(.*[^0-9])(_([0-9]+))*(\.[a-z]+)$', '\1_' || TBSP_FILE_ID || '\4')
  INTO TBSP_FILE
  FROM DUAL;
  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || NEW_TBSP || ' DATAFILE ''' || TBSP_FILE ||
                      ''' SIZE 100M AUTOEXTEND ON NEXT 100M';
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(' WARNING: tablespace might already exist ');
  END;

  FOR SEG IN (SELECT DISTINCT
                nvl(I.TABLE_OWNER, S.OWNER)       AS OWNER,
                S.SEGMENT_NAME,
                S.SEGMENT_TYPE,
                S.PARTITION_NAME,
                nvl(L.TABLE_NAME, I.TABLE_NAME)   AS TABLE_NAME,
                nvl(L.COLUMN_NAME, C.COLUMN_NAME) AS COLUMN_NAME,
                P.PARTITION_NAME                  AS REAL_PARTITION_NAME
              FROM DBA_SEGMENTS S
                LEFT OUTER JOIN DBA_LOBS L
                  ON L.OWNER = S.OWNER AND L.SEGMENT_NAME = S.SEGMENT_NAME
                LEFT OUTER JOIN DBA_LOB_PARTITIONS P
                  ON P.TABLE_OWNER = L.OWNER AND P.TABLE_NAME = L.TABLE_NAME AND P.COLUMN_NAME = L.COLUMN_NAME
                     AND S.PARTITION_NAME = P.LOB_PARTITION_NAME
                LEFT OUTER JOIN DBA_INDEXES I
                  ON I.OWNER = S.OWNER AND I.INDEX_NAME = S.SEGMENT_NAME
                LEFT OUTER JOIN DBA_LOBS C
                  ON C.INDEX_NAME = I.INDEX_NAME
              WHERE S.TABLESPACE_NAME = OLD_TBSP AND S.SEGMENT_NAME NOT LIKE 'BIN$%'
                    AND S.SEGMENT_TYPE NOT IN ('LOBINDEX', 'INDEX PARTITION')
              ORDER BY S.SEGMENT_TYPE DESC) LOOP
    DBMS_OUTPUT.put_line(SEG.SEGMENT_TYPE || ' ' || SEG.SEGMENT_NAME);
    IF SEG.SEGMENT_TYPE = 'INDEX'
    THEN
      EXEC_IMM := 'ALTER INDEX ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" REBUILD TABLESPACE ' || NEW_TBSP ||
                  ' ONLINE';
    ELSIF SEG.SEGMENT_TYPE = 'INDEX SUBPARTITION'
      THEN
        EXEC_IMM :=
        'ALTER INDEX ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" REBUILD SUBPARTITION ' || SEG.PARTITION_NAME ||
        ' TABLESPACE ' || NEW_TBSP || '';
    ELSIF SEG.SEGMENT_TYPE = 'INDEX PARTITION'
      THEN
        EXEC_IMM :=
        'ALTER INDEX ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" REBUILD PARTITION ' || SEG.PARTITION_NAME ||
        ' TABLESPACE ' || NEW_TBSP || '';
    ELSIF SEG.SEGMENT_TYPE = 'LOB PARTITION'
      THEN
        EXEC_IMM :=
        'alter table ' || SEG.OWNER || '."' || SEG.TABLE_NAME || '" move partition ' || SEG.REAL_PARTITION_NAME ||
        ' lob (' || SEG.COLUMN_NAME || ') store as ( tablespace ' || NEW_TBSP || ' )';
    ELSIF SEG.SEGMENT_TYPE = 'LOBSEGMENT'
      THEN
        EXEC_IMM := 'ALTER TABLE ' || SEG.OWNER || '."' || SEG.TABLE_NAME || '" MOVE LOB(' || SEG.COLUMN_NAME ||
                    ') STORE AS (TABLESPACE ' || NEW_TBSP || ')';
    ELSIF SEG.SEGMENT_TYPE = 'TABLE'
      THEN
        EXEC_IMM := 'ALTER TABLE ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" MOVE TABLESPACE ' || NEW_TBSP || '';
    ELSIF SEG.SEGMENT_TYPE = 'TABLE PARTITION'
      THEN
        EXEC_IMM :=
        'ALTER TABLE ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" MOVE PARTITION ' || SEG.PARTITION_NAME ||
        ' TABLESPACE ' || NEW_TBSP || ' NOLOGGING';
    ELSIF SEG.SEGMENT_TYPE = 'TABLE SUBPARTITION'
      THEN
        EXEC_IMM :=
        'ALTER TABLE ' || SEG.OWNER || '."' || SEG.SEGMENT_NAME || '" MOVE SUBPARTITION ' || SEG.PARTITION_NAME ||
        ' TABLESPACE ' || NEW_TBSP || ' PARALLEL (DEGREE 2) ';
    END IF;
    DBMS_OUTPUT.put_line(EXEC_IMM);
    EXECUTE IMMEDIATE EXEC_IMM;
  END LOOP;

  FOR I IN (SELECT *
            FROM DBA_INDEXES
            WHERE TABLESPACE_NAME = OLD_TBSP) LOOP

    EXECUTE IMMEDIATE 'ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' REBUILD TABLESPACE ' || NEW_TBSP || '';
  END LOOP;

  DBMS_OUTPUT.put_line('Execute the following commands:');
  DBMS_OUTPUT.put_line('DROP TABLESPACE ' || OLD_TBSP || ' INCLUDING CONTENTS AND DATAFILES;');
  DBMS_OUTPUT.put_line('ALTER TABLESPACE ' || NEW_TBSP || ' RENAME TO ' || OLD_TBSP || ';');

END;
/