Restore tables accidentally deleted from Oracle

View tables in the recycle bin

Select object_name, original_name, partition_name, type, ts_name, createtime, droptime from recyclebin;

Restore table

SQL> flashback table test_drop to before drop; or
SQL> flashback table "bin $ B + xkko1rs5k10uko9bfmua = $0" to before drop;

Note: The full-text index cannot be restored because it must be available in 9i or 10 Gb or later versions.

References:

Using the flash back table feature in Oracle Database 10g, you can easily restore accidentally deleted tables.

The following is a situation that should not happen frequently: the user deleted a very important table-of course, accidentally deleted-and needs to be restored as soon as possible. (In some cases, this unfortunate user may be DBA !)

Oracle9i Database introduces the flash-back query option to retrieve data at a certain time point in the past, but it cannot flash-back DDL operations, such as table deletion operations. The only restoration method is to restore the table space at a time point in another database, and then re-create the table in the current database using the Export/Import or other methods. This process requires DBA to do a lot of work and takes valuable time, not to mention using another database for cloning.

Use the flash back table feature in Oracle Database 10g, which makes the restoration process of the deleted table as simple as executing several statements. Let's see how this feature works.

Delete the table!

First, let's check the table in the current mode.

SQL> select * From tab;

Tname

Tabtype

Clusterid

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

Recycletest

Table

Now, we accidentally deleted the table:

SQL> drop table recycletest;

Table dropped.

Now let's check the status of the table.

SQL> select * From tab;

Tname

Tabtype

Clusterid

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

Bin $04 lhcpndanfgmaaaaaanpw = $0 table

The recycletest table does not exist, but note that the new table bin $04 lhcpndanfgmaaaaaanpw == 0 is displayed. This is what happened: the deleted table recycletest does not completely disappear, but is renamed to a name defined by the system. It exists in the same tablespace and has the same structure as the original table. If indexes or triggers are defined on the table, they are also renamed using the same naming rules as the table. Any relevant source (such as the process) is invalid. The triggers and indexes of the original table are changed to the renamed table bin $04 lhcpndanfgmaaaaaanpw ==$ 0, maintain the complete object structure of the table to be deleted.

Tables and their related objects are placed in a logical container called "recycle bin", which is similar to the recycle bin on your PC. However, objects are not deleted from their original tablespace; they still occupy space. The recycle bin is only a logical structure used to list the directories of deleted objects. Run the following command at the SQL * Plus prompt to view its content (you need to use SQL * Plus 10.1 for this operation ):

SQL> show recyclebin

Original Name

Recyclebin name

Object Type

Drop time

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

Recycletest

Bin $04 lhcpndanfgmaaaaaanpw = $0 table

2004-02-16: 21: 13: 31

The results show the original table name recycletest and the new name in the recycle bin. The name is the same as the name of the new table created after the deletion. (Note: The exact name may vary depending on the platform .) To restore the table, you must run 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 is restored effortlessly. If you view the recycle bin, it will be empty.

Remember, placing a table in the recycle bin does not release space in the original tablespace. To release space, run the following command to clear the recycle bin:

Purge recyclebin;

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

Drop table recycletest purge;

This command does not rename the table as the recycle bin name, But permanently deletes the table, just like the version earlier than 10 Gb.

Manage Recycle Bin

If the table is not actually deleted during this process and the tablespace is not released, what will happen when the deleted object occupies all the space?

The answer is simple: this does not happen at all. When the tablespace is fully occupied by the recycle bin, and the data file must be expanded to accommodate more data, it can be said that the tablespace is under "Space pressure. In this case, the object is automatically cleared from the recycle bin in the first-in-first-out mode. Before deleting a table, related objects (such as indexes) are deleted.

Similarly, the space pressure may be caused by the user quota defined by a specific tablespace. The tablespace may have enough free space, but the user may have used up the part allocated in the tablespace. In this case, Oracle automatically clears the objects belonging to the user in the tablespace.

In addition, you can manually control the recycle bin in several ways. If you need to clear a table named test from the recycle bin after deleting it, you can run

Purge table test;

Or use the name in its recycle bin:

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

This command will delete the table test and all related objects, such as indexes and constraints, from the recycle bin, thus saving space. However, to permanently delete an index from the recycle bin, run the following command:

Purge index in_test000001;

This command only deletes the index and keeps the copy of the table in the recycle bin.

Sometimes clearing at a higher level may be useful. For example, you may want to clear all objects in the tablespace users recycle bin. Run the following command:

Purge tablespace users;

You may want to clear the recycle bin only for specific users in the tablespace. In a data warehouse environment, you can create and delete many temporary tables. This method may be useful. You can change the preceding command to clear only specific users:

Purge tablespace users user Scott;

Users such as Scott can use the following command to clear their recycle bin:

Purge recyclebin;

DBA can use the following command to clear all objects in any tablespace:

Purge dba_recyclebin;

You can see that you can manage the recycle bin in different ways to meet specific needs.

Table version and flashback

You may often create and delete the same table multiple times, for example:

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;

In this case, if you want to perform a flashback operation on table test, what is the value of col1 in the column? The general idea may be that the first version of the table is retrieved from the recycle bin. The value of col1 in the column is 1. In fact, the third version of the table is retrieved, not the first version. Therefore, the value of col1 is 3, not 1.

In this case, you can retrieve other versions of the deleted table. However, the existence of table test does not allow this situation. 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 the second version to test2. The values of col1 in columns test1 and Test2 are 1 and 2, respectively. Or,

Use the specific recycle bin name of the table for restoration. To do this, first identify the table recycle bin name and then execute:

Flashback table "bin $04 lhcpnoanfgmaaaaaanpw = $0" to before drop Rename to Test2;

Flashback table "bin $04 lhcpnqanfgmaaaaaanpw = $0" to before drop Rename to test1;

These statements restore the two versions of the deleted table.

Warning ......

The delete feature restores the original table name, but the index, trigger, and other objects do not restore the original name. They still use the recycle bin name. The source defined in the table (such as views and processes) is not re-compiled and remains invalid. You must manually obtain the original names and apply them to the flashback table.

The information is stored in the view named user_recyclebin. Before you perform a flashback operation on a table, use the following query to retrieve the original name.

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 $04 lhcpnianfgmaaaaaanpw = $0 in_rt_01

Index

Bin $04 lhcpnganfgmaaaaaanpw = $0 tr_rt

Trigger

After the table is flashed back, the indexes and triggers on the recycletest table are named according to the object_name column. Based on the above query, you can use the original name to rename the object, as shown below:

Alter Index "bin $04 lhcpnianfgmaaaaaanpw = $0" RENAME to in_rt_01;

Alter trigger "bin $04 lhcpnganfgmaaaaaanpw ==$ 0" RENAME to tr_rt;

A notable exception is Bitmap indexes. When you delete Bitmap indexes, they are not placed in the recycle bin. Therefore, they cannot be retrieved. The constraint name cannot be retrieved from the view. They must be renamed from other sources.
Other functions of the flashback table

The flash-Back table deletion function is not limited to restoring table deletion operations. Similar to flashback query, you can also use it to restore the table to different time points, such as flashback table tmm2076 to timestamp to_timestamp ('2017-05-22
12:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
A ORA-08189 error pops up and you need to execute the following command first:
The alter table tmm2076 enable row movement command allows Oracle to modify the rowid assigned to the row.

Then, flashback is used to restore the data.