This code will generate a file that can be run to flashback tables from the recyclebin that have been dropped. Flashback needs to be enabled and therefore this is only available in Oracle versions >10g.
NOTE: Step 10 requires a list of tables to be entered into the 'in' list.
1. Get the names of the table(s) you want to flash back to before the drop
2. . oraenv
3. SET YOUR SID
4. sqlplus /nolog
5. SQL> connect USERID/PASSWORD This is the user who owns the tables you are flashing back.
6. SQL> set feedback off
7. SQL> set hea off
8. SQL> set pages 2000
9. SQL> spool /tmp/flashbacktables.sql
10. SQL> select 'FLASHBACK TABLE "'||object_name||'" TO BEFORE DROP;' from recyclebin where ORIGINAL_NAME in ('TABLE LIST…','….','…');
11. CHECK THE FLASHBACKTABLES.SQL
12. SQL> @/tmp/flashbacktables.sql