DB - Flashback Drop

English Version 

Apos um drop table (excepto se foi usado purge), Oracle move a tabela para o recyclebin, neste post partilho como recuperar uma tabela depois de um  drop table, - e possivel tambem consultar o contedudo de tabelas no recycle bin.

As '/ as sysdba',

SQL> alter user hr identified by hr account unlock;
User altered.

SQL> conn hr/hr 
Connected.

SQL> exec dbms_stats.gather_schema_stats('HR');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
JOB_HISTORY                            10
EMPLOYEES                             107
COUNTRIES                              25
JOBS                                   19
DEPARTMENTS                            27
LOCATIONS                              23
REGIONS                                 4

7 rows selected.

SQL> select * from REGIONS;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> create table regions_4_drop as select * from regions;
Table created.

SQL> drop table regions_4_drop;
Table dropped.

SQL> select * from regions_4_drop;
select * from regions_4_drop
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> -- upsss !!!! , let's see what we can do 

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
REGIONS_4_DROP   BIN$ZS8TMdYUqM3gUKjAZAEUvw==$0 TABLE        2018-02-14:15:57:49
       
SQL> select * from "BIN$ZS8TMdYUqM3gUKjAZAEUvw==$0";

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> flashback table REGIONS_4_DROP to before drop;
Flashback complete.

SQL> select * from regions_4_drop;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> drop table regions_4_drop purge;
Table dropped.

SQL> show recyclebin;

SQL> -- upsss !!!! ,  it's gone - good luck with backups / see the flashback database 

Obrigado pela leitura.
Espero que este post o tenha ajudado.

Versao Portuguesa 

After drop a table Oracle moves the table to the recycle bin [execpt with purge option], on this  post i'm sharing how to recover a from a "drop table" ,  how to check the data in recycle bin.

As '/ as sysdba',

SQL> alter user hr identified by hr account unlock;
User altered.

SQL> conn hr/hr 
Connected.

SQL> exec dbms_stats.gather_schema_stats('HR');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
JOB_HISTORY                            10
EMPLOYEES                             107
COUNTRIES                              25
JOBS                                   19
DEPARTMENTS                            27
LOCATIONS                              23
REGIONS                                 4

7 rows selected.

SQL> select * from REGIONS;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> create table regions_4_drop as select * from regions;
Table created.

SQL> drop table regions_4_drop;
Table dropped.

SQL> select * from regions_4_drop;
select * from regions_4_drop
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> -- upsss !!!! , let's see what we can do 

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
REGIONS_4_DROP   BIN$ZS8TMdYUqM3gUKjAZAEUvw==$0 TABLE        2018-02-14:15:57:49
       
SQL> select * from "BIN$ZS8TMdYUqM3gUKjAZAEUvw==$0";

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> flashback table REGIONS_4_DROP to before drop;
Flashback complete.

SQL> select * from regions_4_drop;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

SQL> drop table regions_4_drop purge;
Table dropped.

SQL> show recyclebin;

SQL> -- upsss !!!! ,  it's gone - good luck with backups 

Thank you for reading.

hope this post was helpful.