Con este espacio pretendo crear un rincón de información relacionda con las bases de datos Oracle, sus funcionalidades, características, noticias.... etc. Interaré hacer pequeños artículos con ejemplos demostrativos para que resulte más fácil leerlos. No dudéis en mandar vuestros comentarios, críticas, aclaraciones... etc.

Información del Autor

Llevo más de diez años trabajando como DBA Oracle en diferentes compañías y desde el 2004 lo hago de forma freelance a través de una sociedad creada por mí, Infor Consult Soluciones (http://www.inforconsult.es). Si tienes cualquier duda, comentario o propuesta que hacerme, no lo dudes.

jueves, 22 de noviembre de 2007

La papelera de reciclaje de Oracle10g: Recycle Bin

¿Qué es la papelera de reciclaje de Oracle?
A partir de Oracle10g se incorpora esta nueva funcionalidad, denominada FLASHBACK DROP, que se viene a unir a las demás características que conforman el capítulo de las funcionalidades FLASHBACK. La idea es la misma que tienen algunos sistemas operativos con su papelera de reciclaje, es decir, disponer de una ubicación donde colocar los objetos borrados con el fin de poder recuperarlos después. Por supuesto, todo esto se hace de forma transparente para el usuario.

¿Cómo funciona la papelera de reciclaje?
Cuando un usuario lanza la orden DROP sobre una tabla, en realidad, lo que hace Oracle es renombrar ese objeto y todas sus dependencias, tales como triggers, índices, constraints etc. El nombre que recibe el objeto es generado automáticamente por Oracle pero siempre está prefijado por BIN$. Como sólo se produce un renombrado, los objetos siguen existiendo y ocupando espacio sus correspondientes tablespaces. El objeto sólo se borra definitavemente si el usuario o el administrador usan el comando PURGE o el espacioocupado por ese objeto es necesario para acomodar una nueva extensión de un objeto vivo (por esto último, se dice que FLASHBACK DROP no es intrusivo ya que si el espacio ocupado por la papelera es necesario se libera automáticamente).Por el contrario, cuando se borra un tablespace sus objetos no son pasados a la papelera sino que son borrados definitivamente, incluyendo los objetos que estén en la papelera de ese tablespace. Lo mismo ocurre cuando se borra un usuario con opción CASCADE.

¿Se puede ver el contenido de las tablas de la papelera?
Sí. Las tablas borradas que residen en la papelera pueden ser consultados con las mismas restricciones que tenían antes de ser borrados. Para consultarlos hay que usar el nombre nuevo que le asigna Oracle entrecomillándolo.

¿Se puede modificar el contenido de las tablas de la papelera?
No. Las tablas de la papelera son de solo lectura. Si se intenta un transacción DML se obtiene el siguiente error: ORA-38301: no se puede realizar DDL/DML en objetos de la papelera de reciclaje.

¿Se puede restaurar un objeto borrado que está en la papelera?
Sí. Al restaurar un objeto también se restauran sus objetos dependientes, pero estos conservan los nombres que tenían en la papelera. Se puede conseguir de varias formas:
  1. Haciendo un CREATE TABLE nombre_tabla... AS SELECT * FROM nombre_tabla_borrada.
  2. Creando la tabla manualmente mediante CREATE TABLE y después rellenarla usando INSERT INTO nombre_tabla AS SELECT * FROM nombre_tabla_borrada.
  3. Usando FLASHBACK TABLE nombre_tabla TO BEFORE DROP [RENAME TO nuevo_nombre_tabla]. En este caso el nombre_tabla puede ser el nombre original de la tabla asignado por Oracle al meterla en la papelera. IMPORTANTE: si existe más de una tabla en la papelera con el mismo nombre que la tabla original, Oracle restauraría la borrada más o el recientemente. Por esta razón, es más seguro usar el nombre generado por Oracle. Por último, con la cláusula RENAME TO se puede indicar el nuevo nombre que tendrá la tabla después de ser recuperada.
¿Cómo es la papelera de reciclaje?
La vista RECYCLEBIN es un sinónimo que apunta a la vista USER_RECYCLEBIN que su vez apunta a la tabla SYS.RECYCLEBIN$. Por tanto, es indiferente consultar RECYCLEBIN o USER_RECYCLEBIN. La estructura de esta vista es:

Nombre              ¿Nulo?            Tipo
------------------- ----------------- ------------
OBJECT_NAME         NOT NULL          VARCHAR2(30)
ORIGINAL_NAME                         VARCHAR2(32)
OPERATION                             VARCHAR2(9)
TYPE                                  VARCHAR2(25)
TS_NAME                               VARCHAR2(30)
CREATETIME                            VARCHAR2(19)
DROPTIME                              VARCHAR2(19)
DROPSCN                               NUMBER
PARTITION_NAME                        VARCHAR2(32)
CAN_UNDROP                            VARCHAR2(3)
CAN_PURGE                             VARCHAR2(3)
RELATED             NOT NULL          NUMBER
BASE_OBJECT         NOT NULL          NUMBER
PURGE_OBJECT        NOT NULL          NUMBER
SPACE                                 NUMBER

Cada vez que un objeto es borrado con el comando DROP se crea una entrada nueva en esta vista que tiene el nombre nuevo dado al objeto, prefijado por BIN$, su nombre original, el tablespace en el que reside, la fecha y hora de eliminación, el espacio que ocupa, etc.

¿Cuál es la sintaxis del comando PURGE?
Como ya se ha indicado, la eliminación de los objetos residentes en la papelera se puede hacer manualmente a través del comando PURGE. Con este comando sólo se pueden borrar los objetos del usuario que lanza la orden, salvo que se disponga de permiso SYSDBA o DROP ANY. Las variantes que existen son:
  • PURGE TABLE nombre_tabla: Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.
  • PURGE INDEX nombre_índice: Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.
  • PURGE RECYCLEBIN: Elimina todos los objetos (del usuario que lanza la orden) de la papelera.
  • PURGE DBA_RECYCLEBIN: Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.
  • PURGE TABLESPACE nombre_tablespace: Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.
  • PURGE TABLESPACE nombre_tablespace USER nombre_usuario: Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.
¿Se puede pasar por alto la Papelera de Reciclaje?
Sí. Existen dos modos para evitar que el objeto borrado pase a la papelera:

  • A través de un parámetro del init.ora. En Oracle10g R1 es _RECYCLEBIN, un parámetro oculto porque empieza por el carácter subrayado "_", que por omisión está a TRUE. Asignándole FALSE hacemos que Oracle10g no use la papelera y elimine el objeto definitivamente, como en anteriores versiones. En Oracle10g R2 se usa el parámetro RECYCLEBIN, éste ya no oculto, cuyos valores son ON y OFF siendo ON el valor por omisión. Éste parámetro es dinámico por lo que se puede cambiar para la sesión o para la Instancia.
  • Usando la cláusula PURGE del comando DROP. Con esta opción conseguimos el mismo efecto pero hay que indicarlo en cada comando DROP que usemos.
Ejemplo completo en Oracle10g Release 2:
Creamos una tabla cualquiera llama T1:

SQL> create table t1 (c number(1)) tablespace users;
Tabla creada.


Comprobamos si existe alguna tabla borrada en la papelera:

SQL> select * from recyclebin; -- o SHOW RECYCLEBIN
ninguna fila seleccionada.


Borramos la tabla T1:

SQL> drop table t1;
Tabla borrada.


Comprobamos que la tabla está ahora en la papelera:

SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ ---------------
BIN$ACEfotX6TRqgiRnY6LN6nQ==$0 T1


Volvemos a crear de nuevo la tabla T1 comprobando que la borró de verdad (si no la hubiera borrado no nos dejaría volver a crearla):

SQL> create table t1 (c number(1)) tablespace users;
Tabla creada.


La volvemos a borrar:

SQL> drop table t1;
Tabla borrada.


Comprobamos que ahora están las dos tablas en la papelera:

SQL> select object_name, original_name from recyclebin;

SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ ---------------
BIN$ACEfotX6TRqgiRnY6LN6nQ==$0 T1
BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0 T1

Intentamos acceder a alguna de las tablas borradas en selección e inserción:

SQL> select * from "BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0";
ninguna fila seleccionada

SQL> insert into "BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0" values (1);
insert into "BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0" values (1)* ERROR en línea 1:
ORA-38301: no se puede realizar DDL/DML en objetos de la papelera de reciclaje.

Eliminamos la tabla T1 de la papelera. Como hay dos borra la T1 más antigua:

SQL> purge table t1;
Tabla depurada.


SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ -------------
BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0 T1


Volvemos a crear la tabla T1:

SQL> create table t1 (c number(1)) tablespace users;
Tabla creada.


Ahora la borramos sin dejarla ir a la papelera de reciclaje y lo comprobamos:

SQL> drop table t1 PURGE;
Tabla creada.

SQL> select object_name, original_name from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME
------------------------------ -------------
BIN$ZdHqVQ+nS0OJsW83y8BEoQ==$0 T1


Por último, restauramos la tabla T1 desde la papelera de reciclaje (si existiese ya una tabla con el nombre T1 obtendríamos el error ORA-38312: el nombre original lo ha utilizado un objeto existente). En este caso usaríamos la cláusula vista anteriormente RENAME TO nuevo_nombre:

SQL> flashback table T1 to before drop;
Flashback terminado.

SQL> select object_name, original_name from recyclebin;
ninguna fila seleccionada


Y hasta aquí por hoy con este artículo. Espero que te haya sido útil. Por favor, deja
tus comentarios, correciones o sugerencias.

Juan Lorenzo Arellano
Oracle DBA
Infor Consult Soluciones
www.inforconsult.es

10 comentarios:

Anónimo dijo...

Excelente!! Tienes una virtud de redactar claramente y directo a solucionar el problema . me sirvio de mucha ayuda

Juan Lorenzo Arellano dijo...

Me alegra haberte ayudado.

Idex dijo...

Felicitaciones, muy buena y clara explicación (Saludos desde Colombia)

Zonas de dolor dijo...
Este comentario ha sido eliminado por el autor.
Zulkas dijo...

Muchas gracis por esta entrada tan esclarecedora. Me ha servido de mucho.

He incluido el artículo en mi wiki citandote como autor.
EnlaceDe nuevo gracias, Un saludo

luis dijo...

muchas gracias, es tan claro como conciso.

Rocio Baez dijo...

Eres lo maximo!!!!!!!!!!

Grupo de Estudiantes Catedra Analisis de Producto UNERS Sem 2010 I dijo...

Juan esta propiedad estaria bien otorgarsela a un grupo de desarrolladores o consideras tu que es netamente para los DBA ?

Grupo de Estudiantes Catedra Analisis de Producto UNERS Sem 2010 I dijo...

saludos a rocio !!!!

Juan Lorenzo Arellano dijo...

Yo diría que es una funcionalidad mixta. En un principio es más para que desarrollo la explote, pero sobre sistemas productivos, quizá es el DBA el que borra un tabla sin querer durante un mantenimiento.
Por tanto, no puede decantarme claramente por un perfil u otro.
Pasa exactamente lo mismo con las funcionalidad flashback.
Gracias por leerme y preguntar.

Por cierto, ¿¿quién es Rocio??