Cambiar TEMP Tablespace eliminando tempfile

Introducción
A diferencia de los tablespace de datos, los temporales no hacen un uso paralelo de los datafiles asignados <en este caso tempfiles>. Oracle irá llenando el tempfile hasta que no pueda y entonces utilice el siguiente tempfile, es decir, Oracle se comportará de forma secuencial.

Este hecho hace que la estrucutra de los tablespace temporales, difiera un poco de los de datos/indices. Vamos a ver paso a paso, cómo podemos crear una estructura de un único tempfile en nuestro schema y de esta forma, no desperdiciar espacio.

Ver Tablespace Temporales
Vamos a ver cómo podemos obtener los tablespace temporales que tenemos asignados a nuestra base de datos -aquí hay muchas formas de hacerlo, pero vamos a utilizar las vistas dinámicas <v$> para ello, aunque podemos utilizar RMAN si queremos-

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 18 13:33:42 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

SQL>  SELECT TS# FROM V$TEMPFILE GROUP BY TS#;

       TS#
----------
         3
        10

SQL>  SELECT NAME FROM V$TABLESPACE WHERE TS# IN (3,10);

NAME
------------------------------
TEMP
TMPUSR

Vamos a eliminar el tempfile asignado al tablespace <TEMP> y sustituirlo por uno nuevo de 2GB en otra ubicación. Para ello, vamos a utilizar algunos de los pasos de Cómo mover un Tempfile en Caliente


Obtener los Tempfile Asignados al Tablespace
Como queremos obtener los tempfile asignados al tablespace <TEMP>, y éste tiene como TS# el valor <3>, simplemente lanzaremos una consulta a <v$tempfile> para ver que tempfiles tiene asignado.

SQL> SELECT NAME FROM V$TEMPFILE WHERE TS# = 3;

NAME
--------------------------------------------------------------------------------
/u02/oradata/TESTDB/temp01.dbf

Lo primero que debemos hacer es Añadir un Tempfile a un tablespace temporal para, que de esta forma, podamos poner en offline el tempfile que queremos eliminar. Una vez puesto en offline, simplemente haremos un drop y tendremos nuestro tablespace con la estructura nueva. Veamos los pasos con más detalle.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 18 12:18:00 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

SQL>  ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/TESTDB/temp03.dbf' SIZE 2048M AUTOEXTEND OFF;

Tablespace altered.

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' OFFLINE;

Database altered.

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP;

Database altered.

SQL> SELECT NAME FROM V$TEMPFILE WHERE TS# = 3;

NAME
--------------------------------------------------------------------------------
/u03/oradata/TESTDB/temp03.dbf

SQL> QUIT
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

Referencias

• Comando básicos de Solaris, y no tan básicos 
• Comandos básicos de Administración de Oracle sobre Solaris