10 Gb after Oracle Database: flashback table

Use Oracle Database 10GThe flash back table feature in allows you to easily restore accidentally deleted tables.

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

Oracle9IDatabase introduces the concept of 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 Oracle Database 10GFlash Back table feature, which makes the recovery 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 lhcpndanfgmaaaanpw = $0 table
 

 

The recycletest table does not exist, but note that the new table appears.Bin $04 lhcpndanfgmaaaaaanpw = $0. 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 = $0To 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 recyclebinOriginal Name recyclebin name object type drop time ---------------- hour ------------ ------------------ 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 useFlashback tableCommand:

 

 
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 name in the recycle bin, but permanently deletes the table, as shown in Figure 10GThe previous versions are the same.

 

Manage Recycle Bin

 

If no table is actually deleted during this process-Therefore, the tablespace is not released.-So 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 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 rows = $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, typefrom user_recyclebinwhere base_object = (select base_object from user_recyclebinwhere original_name = 'recycletest') and original_name! = 'Recycletest'; object_name original_n type =---------- -------- bin $04 lhcpnianfgmaaaaaanpw = $0 in_rt_01 indexbin $04 lhcpnganfgmaaaanpw = $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 deleting 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 use it to restore the table to different time points and use the "past" version of the table to replace the whole table. For example, the following statement restores the table to System Change number (SCN) 2202666520.

 

 
Flashback table recycletest to SCN 2202666520;
 

 

This feature uses Oracle Data Pump technology to create different tables, uses the flash back function to fill the data version at the SCN into the table, and then replaces the original table with a new table. To find out the extent to which the table can be flashed back, you can use Oracle Database 10GVersion Control Feature. (For more information, see the content of week 1st in this series .) In the flashback clause, you can specify a time stamp instead of an SCN.