Yesterday I had a developer at my desk who accidentally dropped a few tables. Time for the Recycle Bin thingy!
First check if recycle bin is on. If not, the user is… will be disappointed.
show parameter recyclebin;
After that (if recyclebin is on), check if the dropped table is in the recycle bin.
select object_name, original_name, droptime, owner from dba_recyclebin where owner = 'some owner'; show recyclebin select * from user_recyclebin select * from dba_recyclebin
Now flashback the table.
flashback table <original or recycblebin table name> to before drop;
Easy.
The recyclebin only works for locally managed tablespaces and if the table is created other than in the system tablespace (that is why the recyclebin is empty when testing it with the sys account and not assigning a tablespace).
Now I can write a whole item on this, but I found a very nice description by Natalka Roshak here: http://www.orafaq.com/node/968
Happy consulting!