How to undrop a table in Oracle

When a table in the Oracle databaseis dropped, table is not actually dropped. The dropped table is renamed to other name which starts with BIN$.... The table is stored in recycle area, so the table space is still occupied by the dropped table. This is the new feature in Oracle version 10G. We will try to create the new table, insert data, drop and undrop the 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> drop table testing;

Table dropped.

Now the deleted table is stored in the recycle area. To see the dropped table in the recycle area, issue this SQL commend.

SQL> select object_name, original_name, type, droptime

from recyclebin Where original_name = 'TESTING';

the result is shown below

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

------------------------------ ------------- ----- ---------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING TABLE 2006-09-01:16:10:12

If the user makes the select statement again, the user shall use BIN$HGnc55/7rRPgQPeM/qQoRw==$0 for table name

Select * from BIN$HGnc55/7rRPgQPeM/qQoRw==$0;

Now the user can restore the table by using this SQL statement

SQL> flashback table testing to before drop;

Flashback complete.

In case that we want to completely delete table Testing from database. We have to purge table from the recycle bin area.

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

Table purged.

After purging operation is complete, the table space in the Oracle is released.
Hone your skill on the Oracle database with this book