Reducir tablespace TEMP en ORACLE 10g

Crear el nuevo tablespace temporal de la base de datos, al que llamaremos TEMP2.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE SIZE 2048M AUTOEXTEND ON NEXT 128M MAXSIZE 4096M;

Obsérvese como en la creación de este tablespace se asigna un valor MAXISZE.

Configurar TEMP2 como tablespace temporal por defecto de la base de datos.


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

 Asegurarse que no existan sessiones utilizando el viejo tablespace Temporal

   a.  Buscar el numero de sesiones  desde V$SORT_USAGE: 
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; 

   b.  Buscar el session ID from V$SESSION:

       Si los querys anterios devuelven resultados, entonces lo que haremos sera buscar el SID from the V$SESSION.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; 

    c.  Kill Session:

    Ahora matamos la sesion con IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE; 

Poner en estado OFFLINE el antiguo tablespace temporal. En este punto se observa que no se puede hacer:

ALTER TABLESPACE TEMP OFFLINE;

La bd devuelve el error ORA-03217.

Para solucionarlo, hay que actuar sobre los datafiles del tablespace. En este caso, uno:


ALTER DATABASE TEMPFILE ‘+disco/BD/tempfile/datafile’ OFFLINE

Borrar el antiguo tablespace temporal.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Comprobaremos ahora como los esquemas existentes en la base de datos tienen como tablespace temporal por defecto TEMP2 con la siguiente consulta:

select username,temporary_tablespace from dba_users;