Réduire le tablespace TEMP

Le tablespace temporaire a trop grossi suite à une opération sur la base. Comment le réduire ?

SQL> SELECT tablespace_name, file_name, bytes/1024/1024/1024
2  FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

Oracle 10 et inférieurs

Créer un tablespace "temporaire temporaire" pour le remplacer :

SQL> CREATE TEMPORARY TABLESPACE temp2
2  TEMPFILE '/the/full/path/to/temp2_01.dbf' SIZE 5M REUSE
3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Déclarer ce second tablespace par défaut pour la base :

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Supprimer l'ancien tablespace trop gros :

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Recréer un tablespace (pour retrouver le nom de tablespace de départ, c'est plus propre):

SQL> CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/the/full/path/to/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Le déclarer défaut pour la base :

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Et supprimer le tablespace "temporaire temporaire"

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Oracle 11

Beaucoup plus simple !

SQL> alter tablespace TEMP shrink space keep 1G;