Recycle bin option of Oracle 10g

Its nice to have the new recycle bin option of oracle 10g. If you delete a table mistakenly you can find it from recycle bin and restore it back.

How to query for deleted tables?

select * from recyclebin

or more generally

select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME
------------------------------ ------------- ----- --- -------------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12

How to restore?

flashback table [original table name] to before drop;

How to delete from recycle bin?

purge recyclebin

or if you want to delete only one table

purge table [name of recycled table]

[name of recycled table] is not the original table name. its the table name like BIN$HGnc55/7rRPgQPeM/qQoRw==$0~

For more information visit this site.

Vidula Hasaranga - විදුල හසරංග ~