Recover deleted dropped tables by mistake in Oracle

To view a table in the Recycle Bin

Select Object_name,original_name,partition_name,type,ts_name,createtime,droptime from RecycleBin;

Recovery table

Sql>flashback table Test_drop to before drop;
Sql>flashback table "Bin$b+xkko1rs5k10uko9bfmua==$0" to before drop;

Note: must be supported by version 9i or 10g, flashback cannot restore full-text index

The following is a reference

Easily recover accidentally deleted tables using the Flash-back table feature in Oracle Database 10g

The following is a situation that should not happen but often happens: the user deletes a very important table--certainly accidentally deleted--and needs to be recovered as soon as possible. (At some point, this unfortunate user may be dba! ）

Oracle9i Database introduces the concept of a flashback query option to retrieve data from a point in time, but it does not flash back to DDL operations, such as deleting a table. The only recovery method is to use a point-in-time recovery of the tablespace in another database, and then recreate the table in the current database using an export/import or other method. This process requires the DBA to do a lot of work and spend valuable time, not to mention the use of another database for cloning.

Use the Flashback table feature in Oracle Database 10g, which makes the deleted table recovery process as simple as executing several statements. Let's look at how this feature works.

Delete that watch!

First, let's look at the table in the current schema.

Sql> select * from tab;

Tname

Tabtype

Clusterid

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

Recycletest

TABLE

Now, we accidentally deleted the table:

sql> drop table recycletest;

Table dropped.

Now let's look at 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来):

Sql> Show RecycleBin

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.) To restore the table, all you need to do is use the FLASHBACK Table command:

Sql> FLASHBACK TABLE recycletest to before DROP;

FLASHBACK complete.

Sql> SELECT * from TAB;

Tname

Tabtype

Clusterid

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

Recycletest

TABLE

Look! The table resumed effortlessly. If you look at the Recycle Bin now, it will be empty.

Remember, placing a table in the Recycle Bin does not free up space in the original table space. To free up space, you need to empty the Recycle Bin with the following command:

PURGE RecycleBin;

But what if you want to completely delete the table without using the Flashback feature? In this case, you can permanently delete the table by using the following command:

DROP TABLE recycletest PURGE;

Instead of renaming the table to the name in the Recycle Bin, this command permanently deletes the table, just as it did before 10g.

　　managing the Recycle Bin

What happens if the table is not actually deleted during the process-and thus does not release the table space-then when the deleted object takes up all the space?

The answer is simple: this doesn't happen at all. When the tablespace is fully occupied by the Recycle Bin data, so that the data file must be extended to accommodate more data, you can say that the tablespace is under "spatial pressure." At this point, the object is automatically cleared from the Recycle Bin in FIFO mode. Related objects, such as indexes, are deleted before the table is dropped.

Similarly, the spatial pressure may be caused by a user limit defined by a particular table space. The tablespace may have enough free space, but the user may have exhausted the portion of the table space that is allocated to it. In this case, Oracle automatically clears the object that belongs to that user in the tablespace.

In addition, there are several ways to manually control the Recycle Bin. If you need to purge a specific table named TEST after it has been removed from the Recycle Bin, you can perform

PURGE TABLE TEST;

Or use the name in its Recycle Bin:

PURGE TABLE "bin$04lhcpndanfgmaaaaaanpw==$0";

This command saves space by removing the table TEST and all related objects, such as indexes, constraints, and so on, from the Recycle Bin. However, if you want to permanently delete an index from the Recycle Bin, you can use the following command to do the work:

Purge index in_test1_01;

This command will simply drop the index and leave a copy of the table in the Recycle Bin.

Sometimes cleaning at a higher level may be useful. For example, you may want to clear all objects in the Recycle Bin of the table space USERS. Can perform:

PURGE tablespace USERS;

You may want to empty the Recycle Bin only for a specific user in that tablespace. This approach may be useful in environments where the data warehouse type creates and deletes many temporary tables. You can change the above command to only clear specific users:

PURGE tablespace USERS USER SCOTT;

Users such as SCOTT can use the following command to empty their Recycle Bin

PURGE RecycleBin;

The DBA can use the following command to clear all objects in any table space

PURGE Dba_recyclebin;

As you can see, there are a number of different ways to manage the Recycle Bin to meet specific needs.

　　table version and flash back feature

Users may frequently create and delete the same table multiple times, such as:

CREATE TABLE TEST (COL1 number);

INSERT into TEST VALUES (1);

Commit

DROP TABLE TEST;

CREATE TABLE TEST (COL1 number);

INSERT into TEST VALUES (2);

Commit

DROP TABLE TEST;

CREATE TABLE TEST (COL1 number);

INSERT into TEST VALUES (3);

Commit

DROP TABLE TEST;

At this point, if you want to perform a flashback operation on the table TEST, what should be the value of the column COL1? The general idea may be that the first version of the table is retrieved from the Recycle Bin, and the value of column COL1 is 1. In fact, the third version of the table is retrieved, not the first one. Therefore, the value of column COL1 is 3, not 1.

You can also retrieve other versions of the deleted table at this point. However, the presence of the table TEST does not allow this to happen. You have two options:

Use the Rename option:

FLASHBACK TABLE TEST to before DROP RENAME to TEST2;

FLASHBACK TABLE TEST to before DROP RENAME to TEST1;

These statements restore the first version of the table to TEST1 and restore the second version to TEST2. The values for column COL1 in TEST1 and TEST2 will be 1 and 2, respectively. Or

Use the table's specific Recycle Bin name for recovery. To do this, first identify the name of the Recycle Bin for the table, and then execute:

FLASHBACK TABLE "Bin$04lhcpnoanfgmaaaaaanpw==$0" to before DROP RENAME to TEST2;

FLASHBACK TABLE "Bin$04lhcpnqanfgmaaaaaanpw==$0" to before DROP RENAME to TEST1;

These statements will recover the two versions of the deleted table.

Warning......

Canceling the delete attribute causes the table to revert to its original name, but related objects such as indexes and triggers do not restore the original name, and they still use the name of the Recycle Bin. Sources defined on the table (such as views and procedures) are not recompiled and remain in an invalid state. These original names must be obtained manually and applied to the flashback table.

The information is kept in a view named User_recyclebin. Use the following query to retrieve the original name before you flash back to the table.

SELECT object_name, Original_name, TYPE

From User_recyclebin

WHERE Base_object = (SELECT base_object from User_recyclebin

WHERE original_name = ' recycletest ')

and original_name! = ' recycletest ';

object_name

Original_n TYPE

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

Bin$04lhcpnianfgmaaaaaanpw==$0 in_rt_01

INDEX

Bin$04lhcpnganfgmaaaaaanpw==$0 Tr_rt

TRIGGER

After the table has a flashback operation, the indexes and triggers on table recycletest are named as shown in the Object_name column. Depending on the query above, you can rename the object with the original name, as follows:

ALTER INDEX "bin$04lhcpnianfgmaaaaaanpw==$0" RENAME to in_rt_01;

ALTER TRIGGER "bin$04lhcpnganfgmaaaaaanpw==$0" RENAME to Tr_rt;

One notable exception is the bitmap index. When you delete bitmap indexes, they are not placed in the Recycle Bin-they cannot be retrieved. The constraint name cannot be retrieved from the view either. They must be renamed from other sources.
other uses of the Flash back table

The Flashback delete Table feature is not limited to recovering a table delete operation. Similar to a flashback query, you can also use it to restore a table to a different point in time, such as flashback table tmm2076 to TIMESTAMP to_timestamp (' 2007-05-22
12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
Popup ORA-08189 error, need to execute the following command first:
ALTER TABLE tmm2076 enable row movement The purpose of this command is to allow Oracle to modify the ROWID assigned to the row.

And then flashback, the data is restored.

Recover deleted dropped tables by mistake in Oracle