Indiana University
University Information Technology Services
  
What are archived documents?
Login>>
Login

Login is for authorized groups (e.g., UITS, OVPIT, and TCC) that need access to specialized Knowledge Base documents. Otherwise, simply use the Knowledge Base without logging in.

Close

How do I recover an Oracle database table with recyclebin?

One feature of Oracle 10g is recyclebin, which allows you to recover dropped tables. With recyclebin, any tables you drop do not actually get deleted. Instead, Oracle renames the table and its associated objects to a system-generated name that begins with BIN$.

For example, consider this simple table:

SQL> create table testing (col varchar2(10), row_chng_dt date); Table created. SQL> insert into testing values ('Version1', sysdate); 1 row created. SQL> select * from testing ; COL ROW_CHNG ---------- -------- Version1 16:10:03

Dropping this table will place it in recyclebin:

SQL> drop table testing; Table dropped. SQL> select object_name, original_name, type, droptime from recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME ------------------------------ ------------- ----- --------------- BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING TABLE 2006-09-01:16:10:12

Dropping this table caused it to be renamed. The table data is still there and you can query it as you would a normal table:

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ; COL ROW_CHNG ---------- -------- Version1 16:10:03

Because the table data is still there, it's very easy to "undrop" the table; use the FLASHBACK TABLE... TO BEFORE DROP command. This simply renames the BIN$... table to its original name:

SQL> flashback table testing to before drop; Flashback complete. SQL> select * from testing ; COL ROW_CHNG ---------- -------- Version1 16:10:03 SQL> select * from recyclebin ; no rows selected

Note: After a table has been dropped, it has only been renamed. It is still part of your tablespace, and this space counts against your user tablespace quota. The space will not be reclaimed until you restore the table with the flashback command or use purge to clear the table out of recyclebin:

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ; Table purged.

Oracle will leave objects in recyclebin until the tablespace runs out of space, or until you reach your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation.

This is document awqa in domain all.
Last modified on February 02, 2009.

Comments/Questions/Corrections

Use this form to offer suggestions, corrections, and additions to the Knowledge Base. We welcome your input!

If you are affiliated with Indiana University and would like assistance with a specific computing problem, please use the Ask a Consultant form, or contact your campus Support Center.

Contact Information

Note: We will reply to your comment at this address. If your message concerns a problem receiving email, please enter an alternate email address.