Thursday, April 24, 2008

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 - විදුල හසරංග ~
Post a Comment