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

Investigando un poquito: RECYCLEBIN

Este es uno de los nuevos “features” que Oracle tiene en su versión 10g. Como su nombre lo indica su función es similar al de una papelera de reciclaje, es decir que si borramos una tabla la misma irá a parar a esa “papelera”, gracias a esto podremos recuperar la tabla borrada.

Por default este feature esta activo en Oracle, el parámetro que podemos chequear para verlo es RECYCLEBIN, el cual podremos modificar a nivel de sesión o de sistema.

Entonces, como mencionamos si tenemos el parámetro en ON, si borramos tablas estas no desaparecerán por completo sino que Oracle las renombrara junto con los objetos asociados (triggers, índices, ets.), y ese nombre empezará con el prefijo BIN$.

Disponemos de dos vistas para ver el contenido de la papelera de Oracle:

- USER_RECYCLEBIN (su sinónimo es recyclebin)
- DBA_RECYCLEBIN

Para aclarar un poquito más esto, veamos un ejemplo:

SQL> create table PRUEBA (col1 number, col2 number);

Table created

SQL> drop table PRUEBA;

Table dropped

SQL> select object_name, original_name, can_undrop “und”,

2 droptime from dba_recyclebin;

OBJECT_NAME                     ORIGINAL_NAME  UND    DROPTIME
------------------------------  ------------- ------ -------------------
BIN$gDFsgStAQBPgRAAiZDYAkw==$0  PRUEBA        YES    2010-04-05:16:10:12

Como podemos ver, borramos la tabla pero aun la seguimos teniendo en la papelera, de hecho si quisiéramos podríamos un select al objeto “BIN$gDFsgStAQBPgRAAiZDYAkw==$0”, lo cual nos traería la información que guardaba la tabla PRUEBA

Para poder recuperar la tabla debemos hacer lo siguiente:

SQL> flashback table PRUEBA to before drop;

Con esto tenemos la tabla PRUEBA nuevamente en nuestra base de datos como estaba antes de ser borrada.

Una cosa que nos tiene que quedar claro que cuando borramos una tabla, como vimos, esta no se borra, sino que solo se “renombra” quedando en la papelera de reciclaje. Esto quiere decir que seguimos teniendo el espacio ocupado por dicha tabla. Para borrarla definitivamente debemos “purgar” la papelera de reciclaje, para realizar esta tarea ejecutamos lo siguiente:

SQL> purge table “BIN$gDFsgStAQBPgRAAiZDYAkw==$0”;

Table purged

SQL> select * from recyclebin;

No row selected

Por otro lado, como sabemos, si borramos una tabla oracle la renombra con el prefijo BIN$ y una serie de caracteres, en otro momento volvemos a crear dicha tabla y la volvemos a eliminar, es decir que vamos a tener 2 versiones de la tabla dentro de la “papelera”. Si queremos recuperar la tabla, Oracle siempre nos recuperará la versión más reciente que tenga de la misma en la papelera.
Si queremos recuperar otra versión que no sea la última de una tabla debemos hacer un flashback table pero con el nombre del objeto, es decir “BIN$.....”, ejemplo:

SQL> flashback table “BIN$gDFsgStAQBPgRAAiZDYAkw==$0”

2 to before drop;


OBJETOS ASOCIADOS A UNA TABLA

Qué pasaría si borramos una tabla que contiene un índice o un trigger?
Veamos una serie de pasos que nos muestran el comportamiento que tiene Oracle al respecto:

SQL> create table PRUEBA (col1 number, col2 number);

Table created

SQL> create index IX_PRUEBA on PRUEBA(col2);

Index created

SQL> drop table PRUEBA;

Table dropped

SQL> select object_name, original_name, can_undrop “und”,

2 droptime from recyclebin;

OBJECT_NAME                     ORIGINAL_NAME  UND     DROPTIME
---------------                 -------------  ------  -------------------
BIN$hAur4QSAPDbgRAARhV9U+A==$0  IX_PRUEBA      NO      2010-04-12:12:19:30
BIN$hAur4QSBPDbgRAARhV9U+A==$0  PRUEBA         YES     2010-04-12:12:19:30

Como podemos ver el campo can_undrop referido al índice tiene como valor ‘NO’, esto se debe a que el índice está asociado a la tabla y no le podremos hacer un restore al objeto por sí solo.

Cuando ejecutemos el flashback de la tabla veremos lo siguiente:

SQL> flashback table PRUEBA to before drop;

Flashback complete

SQL> select index_name from user_indexes

2 where table_name = ‘PRUEBA’;

INDEX_NAME
------------------------------
BIN$hAur4QSAPDbgRAARhV9U+A==$0

Como podemos notar Oracle trajo nuevamente la tabla a su schema original, lo hizo junto con su indice pero con la salvedad que el índice no volvió con el nombre anterior ya que Oracle por alguna razón no ‘recrea’ los índices con el mismo nombre que tenían anteriormente sino con el nombre que obtuvieron en la papelera de reciclaje.
Si querés traer la tabla sin su índice lo que se puede hacer es un purge del índice mientras estén en la papelera para luego hacer un flaskback de la tabla.


DESHABILITAR LA PAPELERA DE RECICLAJE

Existen 2 maneras de que al borrar una tabla no vaya a parar a la papelera de reciclaje, el primero es agregando al final de una sentencia ‘DROP TABLE’ la palabra PURGE, con esto nos aseguramos que la tabla se elimine completamente, ejemplo

SQL> create table PRUEBA (col1 number, col2 number);

Table created

SQL> drop table PRUEBA purge;

Table dropped

SQL> select object_name, original_name, can_undrop “und”,

2 droptime from recyclebin;

No rows selected

La otra manera de evitar que vayan las tablas a la papelera cuando las borramos es desactivar directamente a nivel de sesión la papelera de reciclaje, esto lo hacemos de la siguiente manera

SQL> alter session set recyclebin=off;

Session altered

Espero les haya servido

Saludos!
Gondalf.