Oracle Tablespace bzw. Databasefiles verkleinern

Um einen Tablespace auf ein Minimum zu reduzieren, kann folgendes SQL-Kommando genutzt werden:

SELECT DECODE (
    end_block_1,
    NULL,    'ALTER TABLESPACE '
          || TABLESPACE_NAME 
          || ' DROP DATAFILE '''
          || FILE_NAME
          || ''';',
       'ALTER DATABASE DATAFILE '''
          || FILE_NAME
          || ''' RESIZE '
          || CEIL ( (NVL (END_BLOCK_1, 1) * 8192) / 1024 / 1024)
          || 'M;')
    RESIZE_COMMAND
FROM (SELECT FILE_NAME,
             TABLESPACE_NAME,
             FILE_ID,
             FILE_BLOCKS,
             SEGMENT_NAME SEGMENT_NAME_1,
             START_BLOCK START_BLOCK_1,
             END_BLOCK END_BLOCK_1,
             LEAD (SEGMENT_NAME, 1)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  SEGMENT_NAME_2,
             LEAD (START_BLOCK, 1)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  START_BLOCK_2,
             LEAD (END_BLOCK, 1)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  END_BLOCK_2,
             LEAD (SEGMENT_NAME, 2)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  SEGMENT_NAME_3,
             LEAD (START_BLOCK, 2)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  START_BLOCK_3,
             LEAD (END_BLOCK, 2)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  END_BLOCK_3,
             POSITION
         FROM (SELECT FILE_NAME,
                      TABLESPACE_NAME,
                      FILE_ID,
                      FILE_BLOCKS,
                      SEGMENT_NAME,
                      BLOCK_ID START_BLOCK,
                      BLOCK_ID + BLOCKS - 1 END_BLOCK,
                      POSITION
                   FROM (SELECT DISTINCT
                                DDF.TABLESPACE_NAME,
                                DDF.FILE_NAME,
                                DDF.FILE_ID,
                                DDF.BLOCKS FILE_BLOCKS,
                                DE.SEGMENT_NAME,
                                DE.BLOCK_ID,
                                DE.BLOCKS,
                                ROW_NUMBER ()
                                OVER (PARTITION BY DDF.FILE_NAME
                                    ORDER BY DE.BLOCK_ID DESC)
                                    POSITION
                             FROM DBA_DATA_FILES DDF, DBA_EXTENTS DE
                             WHERE   DDF.FILE_ID = DE.FILE_ID(+)
                                         AND DDF.TABLESPACE_NAME LIKE 'TS_%')
                   WHERE POSITION <= 3))
         WHERE POSITION = 1
         ORDER BY FILE_ID;

Dabei muss der entsprechende Tablespace-Name angegeben bzw. korrigiert werden (im Beispiel in der viertletzten Zeile: TS_%). Die erzeugte Ausgabe enthält dann die Änderungsbefehle, um die Databasefiles zu verkleinern bzw. zu löschen:

ALTER DATABASE DATAFILE '/home/oracle/datenbanken/DB/TS_ABC/abc_1.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '/home/oracle/datenbanken/DB/TS_ABC/abc_2.dbf' RESIZE 740M;

ACHTUNG: Wenn die Kommandos ohne Kontrolle abgesetzt werden, kann der geänderte Tablespace so klein werden, dass keine Inserts mehr möglich sind. Also kann unter Umständen die Datenbank stehen bleiben. Daher bitte immer die abgesetzten Kommandos manuell prüfen!!!

Schreibe einen Kommentar

Deine Email-Adresse wird nicht veröffentlicht.