REGENERATE DROPPED TABLE

 Oracle 10G New Feature regenerate dropped table

Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table

To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table. Row movement must be enabled for all tables in the Flashback list. Please refer to row_movement_clause for information on enabling row movement.

To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.

Conditions:

01. Your database is to be archivelog mode
02. Your database has to have db_recovery_file_dest set in init.ora file

SQL> select empno from employee;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.

SQL> drop table employee;

Table dropped.

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

SQL> connect sys@kans10g as sysdba
Enter password: *******
Connected.
SQL> FLASHBACK TABLE scott.employee to BEFORE DROP;

Flashback complete.

SQL> connect scott/tiger@kans10g
Connected.
SQL> select empno from employee;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

14 rows selected.