How to recover a deleted table from Oracle

In 9i, Oracle introduced the concept of flashback, which can return data to a point in time, but is not yet supported for DDL statements such as Drop/truncate. Entering the oracle10g, this flaw has been remedied. The missing table can be retrieved directly from the Flashback command. Very simple and easy to use.

1. First, you can view the tables in the current schema:

Sql> select * from tab;

Tname Tabtype Clusterid

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

Recycletest TABLE

2. Then delete the table:

sql> drop table recycletest;

Table dropped.

3. Check the status of the table:

Sql> select * from tab;

Tname Tabtype Clusterid

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

Bin$04lhcpndanfgmaaaaaanpw==$0 TABLE

Table Recycletest no longer exists, but be aware that a new table bin$04lhcpndanfgmaaaaaanpw==$0 appears. This is what happened: the deleted table Recycletest did not completely disappear, but was renamed to a system-defined name. It exists in the same table space and has the same structure as the original table. If an index or trigger is defined on the table, they are also renamed, using the same naming convention as the table. Any related sources, such as procedures, are invalidated; the triggers and indexes of the original table are instead placed on the renamed table bin$04lhcpndanfgmaaaaaanpw==$0, preserving the complete object structure of the deleted table.

The table and its related objects are placed in a logical container called the Recycle Bin, which resembles the Recycle Bin in your PC. However, the objects are not removed from the table space where they were originally, and they still occupy space there. The Recycle Bin is just a logical structure that lists the deleted object directories. Use the following command at the Sql*plus prompt to view its contents (you need to do this using Sql*plus 10.1 and later):

Sql> Show RecycleBin (or: SELECT * from Dba_recyclebin; SELECT * from User_recyclebin; the command needs to be viewable in Sqlplus of versions above 10g. ）

ORIGINAL name RecycleBin name OBJECT TYPE DROP Time

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

Recycletest bin$04lhcpndanfgmaaaaaanpw==$0 TABLE 2004-02-16:21:13:31

The result shows the original name of the table, Recycletest, and displays the new name in the Recycle Bin, which is the same name as the new table name created after the deletion we see. (Note: The exact name may vary depending on the platform.) ）

4. Then restore the table:

Sql> FLASHBACK TABLE recycletest to before DROP; (or FLASHBACK TABLE "bin$04lhcpndanfgmaaaaaanpw==$0" to before DROP;)

FLASHBACK complete.

You can see that the deleted table has been restored:

Sql> SELECT * from TAB;

Tname Tabtype Clusterid

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

Recycletest TABLE

Remember that placing the deleted table in the Recycle Bin does not release the existing tablespace, and to free up the space, you need to do:

Purge RecycleBin;

So if you want to completely delete a table in 10g, instead of recycling the table into the Recycle Bin:

drop table Recycletest Purge;

Since the drop table is only renamed to the table in Oracle and does not migrate the table space for it, the deleted table will occupy the tablespace if it is not purge. Until the table space is low, Oracle will first go to the Recycle Bin to clean up the tables in the Recycle Bin according to the FIFO principle. Of course, you can also manually clean up the specified table:

Purge table recycletest;

Or use the name of the object in the Recycle Bin:

Purge table "bin$04lhcpndanfgmaaaaaanpw==$0";

This command clears out all objects related to the table, including indexes, constraints, triggers, and so on. If you want to permanently delete an index, you can:

Purge index in_test_01;

This command simply deletes the index of the table without affecting the table (in the Recycle Bin).

In one case, after you drop the EMP, you create an EMP table and then drop again to create the EMP table, then drop:

CREATE TABLE EMP (ID number (10));

drop table emp;

CREATE TABLE EMP (ID number, name VARCHAR2 (100));

drop table emp;

CREATE TABLE EMP (ID number), name VARCHAR2 (+), sex varchar2 (2));

drop table emp;

What table does purge drop after executing purge table emp? Or is it all purge out?

The answer is the same as the previous automatic cleaning principle, each time the first deleted EMP table is removed, the basis for judging is USER_RECYCLEBIN.DROPSCN, according to the size of the value of the purge to determine the order.

In addition, if you want to restore the dropped table, and now you have created the table emp, then how to restore the table (because the table name already exists, cannot be directly restored). Method:

Flashback table EMP to before drop rename to Emp_old;

The table that is about to be restored is named a table of other names. Recovery is the opposite of Purge's order, that is, the last one to be dropped is the first to be restored.

How to recover a deleted table from Oracle