Alle Objekte aus einem Tablespace verschieben

Ich brauche es in der letzten Zeit zu häufig, daher schreibe ich es jetzt endlich mal auch hier hin. Ziel ist es, nach einer Aufräumaktion einen Tablespace drastisch zu verkleinern (In diesem Fall von 600GB runter auf 22GB). Das Vorgehen hier hier:

1. Neuen Tablespace anlegen
2. Objekte verschieben
3. Alten Tablespace droppen (wenn leer!)
4. Alten Tablespace kleiner neu anlegen
5. Objekte wieder zurück schieben
6. Neuen Tablespace droppen
7. Sich über den vielen freien Speicher auf der Festplatte freuen 😉

Schritt 1: Neuen Tablespace anlegen

CREATE TABLESPACE TSP_AUX datafile size 128m autoextend on maxsize unlimited;
ALTER TABLESPACE TSP_AUX add datafile size 128m autoextend on maxsize unlimited;
-- so viele Datafiles hinzufügen, wie man braucht. Man kann auch bigfile nehmen, mag
-- ich aber nicht so sehr. Und da ich auf einer ODA bin und dort OMF im Einsatz ist, gebe
-- ich keine Dateinamen an

Schritt 2: Objekte verschieben

--Tabellen
SELECT 'ALTER TABLE '||owner||'.'||table_name||
       ' MOVE TABLESPACE TSP_AUX;'
FROM dba_tables
WHERE tablespace_name = 'TSP_ORIG'
AND owner NOT IN ('SYS','SYSTEM');

-- Tabellen Partitionen
SELECT 'ALTER TABLE '||table_owner||'.'||table_name||
       ' MOVE PARTITION '||partition_name||
       ' TABLESPACE TSP_AUX;'
FROM dba_tab_partitions
WHERE tablespace_name = 'TSP_ORIG';

-- LOBs
SELECT 'ALTER TABLE '||owner||'.'||table_name||
       ' MOVE LOB ('||column_name||') STORE AS (TABLESPACE TSP_AUX);'
FROM dba_lobs
WHERE tablespace_name = 'TSP_ORIG';

-- Index Partitionen
SELECT 'ALTER INDEX '||index_owner||'.'||index_name||
       ' REBUILD PARTITION '||partition_name||
       ' TABLESPACE TSP_AUX;'
FROM dba_ind_partitions
WHERE tablespace_name = 'TSP_ORIG';

-- Indexes
SELECT 'ALTER INDEX '||owner||'.'||index_name||
       ' REBUILD TABLESPACE TSP_AUX;'
FROM dba_indexes
WHERE tablespace_name = 'TSP_ORIG'
AND owner NOT IN ('SYS','SYSTEM');

Schritt 3: Alten Tablespace löschen (wenn leer)

-- Erstmal schauen, ob noch irgendwas in dem Tablespace drin ist
SELECT count(*) 
FROM dba_segments
WHERE tablespace_name='TSP_ORIG';

-- Manche Indexes haben keine Segmente, daher Extra-Prüfung über diese View
SELECT count(*)
FROM dba_indexes
WHERE tablespace_name='TSP_ORIG';

--Wenn bei beidem "0" zurück kommt, kann gedroppt werden
DROP TABLESPACE TSP_ORIG INCLUDING CONTENTS AND DATAFILES;

Schritt 4-6

Siehe Schritt 1-3, nur mit vertauschten Namen

Schritt 7: Sich über den vielen freien Speicher auf der Festplatte freuen 😉