Trabalhando com Flashback Drop no Oracle 10g (Recycle bin)

Olá,
 

Dropar tabelas por engano sempre foi um problema para usuários e DBAs. Usuários assim que percebem que cometeram um engano e então, percebem que é tarde demais para voltar atrás, pensam: E agora? Bom, historicamente, não há um jeito fácil de recuperar todos estes objetos dropados como tabelas, índices, restrições de integridade, gatilhos, etc.

Nas versões anteriores ao Oracle 10g, se você quisesse recuperar uma tabela que foi dropada acidentalmente, seria necessário importar esta tabela de um arquivo de exportação (dump) recente ou realizar uma recuperação incompleta baseada em horário, onde a recuperação é encerrada após todas as alterações feitas até determinado momento terem sido submetidas à commit.

Um exemplo disso seria:

São 12:00 AM e a tabela PRODUTOS foi dropada às 11:45 AM.
 

Para voltar o banco de dados ao estado anterior ao drop da tabela PRODUTOS, seria necessário realizar os seguintes procedimentos abaixo no que se refere a RECUPERAÇÃO INCOMPLETA BASEADA EM HORÁRIO:

1) Desative o banco de dados e faça backup.
2) Restaure TODOS os arquivos de dados do backup mais recente.
3) Monte o banco de dados.
4) Recupere o banco de dados com o comando abaixo:
   SQL> recover database until time '2006-08-26:11:44:00'
5) sincronize os arquivos de dados com os control files e redo log files:
   SQL> alter database open resetlogs
6) Faça o backup completo do banco de dados fechado.

No exemplo acima, veremos que não só a tabela PRODUTOS será recuperada, mas também todo o banco de dados e isto pode resultar em perda de dados, pois os dados informados após às 11:45 AM serão perdidos e precisarão ser informados novamente.

Para evitar todo este trabalho, o Oracle 10g trouxe a tecnologia Flashback Drop, com o objetivo de fornecer uma segurança para objetos que foram dropados. Quando um usuário dropar uma tabela, o Oracle automaticamente a coloca na Recycle Bin (Lixeira).

O que é a Recycle Bin?
 

Na verdade, a Recycle Bin é um container virtual onde todas as tabelas dropadas residem, fazendo o mesmo papel da lixeira do Windows, o que significa que qualquer tabela que foi dropada poderá ser recuperada. Se uma tabela PRODUTO foi criada na tablespace USERS, esta mesma tabela após ser dropada continuará residindo na tablespace USERS.

Tabelas dropadas e quaisquer objetos associados como índices e constraints na verdade não são movidas para a lixeira. Elas simplesmente são renomeadas com o prefixo BIN$$$. Você poderá ainda continuar acessando os dados da tabela dropada ou até mesmo usar o recurso Flashback Query contra ela. Você pode visualizar os objetos dropados, selecionando-os de uma view chamada RECYCLEBIN.

É importante salientar que os objetos da Recycle Bin permanecerão no banco de dados até que o dono do objeto decida permanentemente remove-los usando o novo comando PURGE (drop table purge). Os objetos na Recycle Bin alocam espaço da mesma forma que os objetos não dropados, e isto em um primeiro momento parece ser desvantajoso, porque estes objetos contam como espaço alocado contra a cota de tablespace do usuário, mas não se preocupe, porque os objetos na Recycle Bin são automaticamente deletados pelo processo que está reclamando espaço livre no tablespace quando os eventos abaixo acontecerem:

• O usuário cria uma tabela ou insere dados que como resultado terá a sua cota excedida no tablespace.

• A tablespace precisa estender o tamanho do arquivo (datafile) para acomodar operações de create/insert.

Portanto, não é preciso desabilitar o recurso da Recycle Bin, como tenho visto em vários forums, onde os usuários se preocupam com o espaço desperdiçado nos tablespaces por alocarem estes objetos que já foram dropados. Bom, vamos então a exemplo prático. Abaixo, irei criar uma tablespace, um usuário e uma tabela especialmente para exemplificar o uso do recurso de Flashback Drop:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on  Qua Nov 8 10:45:01 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -  Production
With the Partitioning, OLAP and Data Mining  options

SQL> CREATE TABLESPACE TESTE
  2    LOGGING
  3    DATAFILE '/u02/oradata/BD01/teste01.dbf'  SIZE 1M
  4    AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED
  5    EXTENT MANAGEMENT LOCAL
  6    SEGMENT SPACE MANAGEMENT AUTO;

Tablespace criado.

SQL> CREATE USER LEGATTI IDENTIFIED BY LEGATTI
  2  DEFAULT TABLESPACE TESTE QUOTA UNLIMITED ON TESTE; 

Usuário criado.

SQL> GRANT CONNECT TO LEGATTI;

Concessão bem-sucedida.

SQL> select
  2     total.tablespace_name              TABLESPACE,
  3     total.bytes/1024                   "SIZE  (KB)",
  4     round(nvl(sum(free.bytes)/1024,0)) "FREE (KB)"
  5  from
  6     (select  tablespace_name,
  7              sum(bytes) bytes
  8        from  sys.dba_data_files
  9        group by tablespace_name) total, dba_free_space  free
 10  where
 11     total.tablespace_name =  free.tablespace_name(+)
 12     and total.tablespace_name = 'TESTE'
 13  group by
 14     total.tablespace_name,
 15     total.bytes;

TABLESPACE                       SIZE (KB)  FREE (KB)
------------------------------  ---------- ----------
TESTE                                1024        960

SQL> connect legatti/legatti
Conectado.

SQL> CREATE TABLE flashback_drop_teste (
  2    id NUMBER contraint PK_TESTE primary key
  3  );

Tabela criada.

SQL> begin
  2    for i in 1..30000
  3    loop
  4     insert into flashback_drop_teste values  (i);
  5    end loop;
  6    commit;
  7  end;
  8  /

Procedimento PL/SQL concluído  com sucesso.

SQL> select
  2     total.tablespace_name              TABLESPACE,
  3     total.bytes/1024                   "SIZE  (KB)",
  4     round(nvl(sum(free.bytes)/1024,0)) "FREE (KB)"
  5  from
  6     (select  tablespace_name,
  7              sum(bytes) bytes
  8        from  sys.dba_data_files
  9        group by tablespace_name) total, dba_free_space  free
 10  where
 11     total.tablespace_name =  free.tablespace_name(+)
 12     and total.tablespace_name = 'TESTE'
 13  group by
 14     total.tablespace_name,
 15     total.bytes;


TABLESPACE                       SIZE (KB)  FREE (KB)
------------------------------  ---------- ----------
TESTE                                 1024          0

SQL> drop table FLASHBACK_DROP_TESTE;

Tabela eliminada.

Acima, eu realizei uma simulação na qual selecionei o espaço atual alocado e disponível no tablespace TESTE e dei uma carga na tabela com 30.000 registros. Após a carga de dados nesta tabela, verificamos que a tablespace foi totalmente alocada com os dados da tabela. Por fim, realizei o DROP da tabela. Bom, após a realização do DROP da tabela consultarei a view recyclebin conforme demonstrado abaixo:

SQL>select  object_name,original_name,type,ts_name from recyclebin;

OBJECT_NAME                     ORIGINAL_NAME         TYPE   TS_NAME
------------------------------  --------------------- ------ -------
BIN$Iby7aGsXBgngQO7IaPspLA==$0  PK_TESTE              INDEX  TESTE
BIN$Iby7aGsYBgngQO7IaPspLA==$0  FLASHBACK_DROP_TESTE  TABLE  TESTE 

Percebemos que, após dropar a tabela, o espaço livre disponível na tablespace voltou a ser como era antes da criação da tabela. Para recuperar a tabela que foi dropada, digitarei o comando abaixo:

SQL> FLASHBACK TABLE flashback_drop_teste TO BEFORE DROP;

Flashback concluído.

SQL> select count(*) from flashback_drop_teste;

COUNT(*)
--------
30000

Como informação adicional, também é possível utilizar o comando abaixo para renomear a tabela ao realizar o Flashback Drop.

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP
  2  RENAME TO flashback_drop_test_old; 

1. Opções para limpar a Recycle Bin

• PURGE TABLE tablename; Tabela específica.

• PURGE INDEX indexname; Índice específico.

• PURGE TABLESPACE ts_name; Todas a tabelas de uma tablespace.

• PURGE TABLESPACE ts_name USER username; Idem para um usuário específico.

• PURGE RECYCLEBIN; Apenas do usuário corrente.

• PURGE DBA_RECYCLEBIN; Toda a Lixeira.

• PURGE TABLE "BIN$V3zj9aOWRImOT89aKyyVSg==$0";

 
2. Informações do Recycle Bin no dicionário de dados

•  recyclebin$

•  recyclebin

•  dba_recyclebin

•  user_recyclebin

3. Habilitar/Desabilitar o recurso em nível de sistema e sessão

    Oracle 10g RELEASE 1

•  ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;

•  ALTER SESSION SET "_recyclebin"=FALSE;

    Oracle 10g RELEASE 2

•  ALTER SESSION SET recyclebin = OFF;

•  ALTER SYSTEM SET recyclebin = OFF;

•  ALTER SESSION SET recyclebin = ON;

•  ALTER SYSTEM SET recyclebin = ON;

4. Restrições de uso da Recycle Bin

• Somente disponível para tablespaces não-sistemas gerenciados localmente.

• Não há um tamanho fixo para a lixeira. O tempo que os objetos permanecem na lixeira podem variar.

• Os objetos que ficam na lixeira são restritos apenas para operações de query (não são permitidos operações DDL ou DML).

• Tabelas e objetos dependentes são colocados na lixeira ao mesmo tempo.

• Tabelas com "Fine Grained Access policies" não são protegidas pela lixeira.

• Tabelas particionadas organizadas por índice não são protegidas pela lixeira.

• A lixeira não preserva integridade referencial.