Tablespace Temporal

Seguramente alguna vez necesiten recrear el tablespace temporal que tiene la base por default. Porque?
Una posible explicación podría ser que nuestro tablespace temporal está configurado en autoextent y por alguna razón aumentó considerablemente su tamaño y no podemos realizarle un RESIZE ya que nos devuelve el siguiente error:

SQL> alter database tempfile

2 '/u01/oradata/PRUEBA/temp01.dbf'

3 resize 100M;

alter database tempfile '/u01/oradata/PRUEBA/temporal01.dbf' resize 100M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Si esto ocurre debemos recurrir a otra estrategia. Tenemos 2 opciones las cuales vamos a tratar de explicar:

OPCION 1

Lo que vamos a hacer es borrar el tablespace y volverlo a crear, pero dado que no podemos borrar el tablespace temporal que la base tiene asignado por default debemos crear uno “provisorio”, creo que se va a entender mejor si vemos los pasos que necesitamos realizar:

SQL> create temporary tablespace temp tempfile

2 '/u01/oradata/PRUEBA/temp01.dbf'

3 size 100M;

Tablespace created.

SQL> alter database default temporary

2 tablespace temp1;

Database altered.

SQL> drop tablespace TEMPORAL including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMPORAL
2 tempfile '/u01/oradata/PRUEBA/temporal01.dbf' size 100M
3 autoextend on next 100M maxsize 32767M;

Tablespace created.

SQL> alter database default temporary tablespace temporal;

Database altered.

SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

OPCION 2

Existe otro método que podemos usar en Oracle 10g (lo podemos usar a partir de 9i) que nos permite borrar el tempfile directamente sin eliminar el tablespace, esto debemos hacerlo sin usuarios trabajando o trabajos batch ejecutándose que usen el temporal.
Para obtener el nombre del tempfile a borrar podemos hacer lo siguiente:

SQL> SELECT tablespace_name, file_name
2 FROM dba_temp_files

3 WHERE tablespace_name = 'TEMPORAL';

TABLESPACE_NAME    FILE_NAME                            
-----------------  --------------------------------
TEMPORAL           /u01/oradata/PRUEBA/temporal01.dbf

Teniendo el nombre podemos proceder a borrarlo, para eso ejecutamos lo siguiente:

SQL> alter database tempfile

2 '/u01/oradata/PRUEBA/temporal01.dbf'

3 drop including datafiles;

Database altered.

SQL> alter tablespace TEMPORAL add tempfile
2 '/u01/oradata/PRUEBA/temporal01.dbf' size 512m
3 autoextend on next 250m maxsize unlimited;

Tablespace altered.

SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_temp_files

3 WHERE tablespace_name = 'TEMPORAL';

TABLESPACE_NAME   FILE_NAME                   
----------------- --------------------------------
TEMPORAL          /u01/oradata/TESTDB/temporal01.dbf

Espero les haya servido

Saludos!
Gondalf