Restore accidentally deleted tables using the 10 Gb flashback feature of Oracle Database

Using the flash back table feature in Oracle Database 10 Gb, 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 !)

Oracle 9i 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 deleting tables. 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 10 Gb, 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:

SQL> drop table recycletest;

Table dropped. Now let's check the Table status.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
-----------------------------------------------
BIN $04 LhcpndanfgMAAAAAANPw =0 0 table recycletest does not exist, but please note that the new table bin $04 LhcpndanfgMAAAAAANPw ==$ 0 appears. 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 the 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 original TABLE name RECYCLETEST is displayed, and the new name in the recycle BIN is displayed, 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! 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 name in the recycle bin, 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-the tablespace is not released-what happens 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"; 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 will only delete the index and keep 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:

1 2 Next Page