Oracle. Reducir de tamaño tablespaces

Un problema muy común y que a mi me ha pasado varias veces es que los tablespaces empiezan a crecer y crecer, y llega un momento que nos quedamos sin espacio en disco. Para resolver esto, sólo existe una solución que yo conozca por ahora para reducir de tamaño tablespaces. Oracle no dispone de un desfragmentador de tablespaces que agrupe todos los datos al comienzo del mismo, y podamos reducir su tamaño. La solución que nos deja es la siguiente.

La solución que propongo consiste en tres sencillos pasos:

1. Crear un nuevo tablespace más pequeño.
2. Copiar el esquema de datos en este nuevo tablespace.
3. Borrar el originario.

Este proceso se puede utilizar también para el tablespace TEMP (es uno de los tablespaces indispensables para que funcione la base de datos).

El ejemplo que voy a seguir es para el tablespace TEMP. Para realizar estos pasos hay que realizar las siguientes tareas:

1. Crear el nuevo tablespace temporal de la base de datos, al que llamaremos TEMP2.
   1. CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE SIZE 512M AUTOEXTEND ON NEXT 64M MAXSIZE 4096M;
2. 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.
   1. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
3. Poner en estado OFFLINE el antiguo tablespace temporal. En este punto se observa que no se puede hacer:
   1. ALTER TABLESPACE TEMP OFFLINE;
4. La bd devuelve el error ORA-03217. Este error indica que los datafiles asociados a este tablespace están ONLINE. Para solucionarlo, hay que poner los datafiles del tablespace en OFFLINE. Para hacer esto hacemos:
   1. ALTER DATABASE TEMPFILE ‘+disco/BD/tempfile/datafile’ OFFLINE;
5. Borrar el antiguo tablespace temporal.
   1. DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
6. Volvemos a intentar asignar el nuevo tablespace TEMP2 como tablespace por defecto de la base de datos.
   1. ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
7. Comprobaremos ahora como los esquemas existentes en la base de datos tienen como tablespace temporal por defecto TEMP2 con la siguiente consulta:
   1. select username,temporary_tablespace from dba_users;

Eso es todo.