Using the flashback feature in Oracle Database 10g, you can recover unexpectedly deleted tables without effort.
The following is a situation that does not happen often: the user deletes a very important table - of course is accidentally deleted - and needs to be recovered as soon as possible. (At some point, this unfortunate user may be DBA!)
Oracle9i Database has introduced the concept of flashback query options to retrieve data in a certain point in time, but it cannot flash back to DDL operations, such as deleting a table. The unique recovery method is to recover the time point of the tablespace in another database, and then re-create the table in the current database using the export / import or other method. This process requires DBA to work a lot of work and cost valuable time, not to mention another database to be cloned.
Please use the flashback feature in Oracle Database 10G, which makes the recovery process of the deleted table as simple as the execution of several statements. Let us see how this feature works.
Delete that 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.
Let us now see the status of the table.
SQL> SELECT * from Tab;
TName Tabtype ClusterID
----------------------------------------------
BIN $ 04LHCPNDANFGMAAAAAAAANPW == $ 0 TABLE
Table RecycleTest has no existence, but please pay attention to the new table BIN $ 04LHCPNDANFGMAAAAAANPW == $ 0. This is what happened: The deleted table RecyTestSt did not disappear completely, but renamed as a name defined by the system. It exists in the same table space with the same structure as the original table. If an index or flip-flop is defined on this table, they are also renamed, using the same naming rules as the table. Any related source (such as the process) is invalid; the original table's trigger and index are changed to place the full object structure of the deleted table on the renamed Table BIN $ 04LHCPNDANFGMAAAAAAANPW == $ 0.
Table and its related objects are placed in a logical container called "recycle bin", which is similar to the recycle bin in your PC. However, the object does not delete from their original table space; they still take up the space there. The recycle bin is just a logical structure listing the deleted object directory. Use the following command at the SQL * PLUS prompt to view its content (you need to use SQL * Plus 10.1 to do this):
SQL> show recyclebin
Original Name Recyclebin Name Object Type Drop Time
------------------------------------------------ -------- --------------------
Recycletest BIN $ 04lhcpndanfgmaaaaaAanpw == $ 0 TABLE 2004-02-16: 21: 13: 31
The result shows the original name RecyTestSt of the table, and the new name in the recycle bin is displayed, which is the same as the new table name created after we see the delete. (Note: The exact name may vary depending on the platform.) To recover the table, what you need to do is to use 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 not hard to recover. If you look at the recycle bin now, it will be empty.
Remember, put the table in the recycle station is not released in the original table space. To release space, you need to use the following command to empty the recycle bin:
Purge recyclebin;
But what should I do if you want to completely delete the table without using a flashback feature? In this case, you can use the following command to permanently delete the table:
DROP TABLE Recycletest Purge
This command does not name the table as a name in the recycle bin, but permanently deletes the table, just like 10G.
Management recycle bin
What happens if there is no actual deletion table in the process - thus does not release table space - then when the deleted object takes up all spaces, what happens?
The answer is very simple: this situation will not appear at all. When the table space is completely full of data, it is necessary to extend the data file to accommodate more data, it can be said that the space is in the case of "spatial pressure". At this point, the object is automatically cleared from the recycle bin in a first-forward first. Before deleting the table, the related objects (such as indexes) are deleted.
Similarly, spatial pressure may be caused by user limits defined by a particular table space. Table space may have sufficient empty space, but the user may use it in the part allocated in the table space. In this case, Oracle automatically clears the objects belonging to the user in the table space.
In addition, there are several ways to manually control the recycle bin. If you need to clear it from the recycle bin after deleting a specific table named Test, you can execute
Purge Table Test;
Or use the name in its recycle bin:
Purge Table "BIN $ 04LHCPNDANFGMAAAAAAAANPW == $ 0";
This command will remove the table TEST and all related objects from the recycle bin, such as index, constraint, etc., thereby saving space. However, if you want to permanently delete an index from the recycle bin, you can use the following command to complete the work:
PURGE INDEX IN_TEST1_01;
This command will only delete the index, and the copy of the table is left in the recycle bin.
Sometimes clears on a higher level may be useful. For example, you might want to clear all objects in the recycle bin of the tablespace User. Can perform:
Purge Tablespace User;
You may wish to empty the recycle bin for a specific user in the table space. In the data warehouse type environment, users create and delete many temporary tables, and this method may be useful. You can change the above command to qualify only a specific user:
Purge TableSpace User Scott;
Users such as Scott can use the following command to empty their recycle bin
Purge recyclebin;
DBA can clear all objects in any table space using the following command
Purge DBA_Recyclebin; It can be seen that the recycle bin can be managed in a variety of different methods to meet specific needs.
Table version and flashback function
Users may often 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 the value of column COL1? The routine idea may consider the first version of the table from the recycle station, the value of column COL1 is 1. In fact, retrieving the third version of the table, not the first. Therefore, the value of column COL1 is 3, not 1.
At this point you can also retrieve other versions of the deleted table. However, the presence of Table Test does not allow this. You have two options:
Use rename options: 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 of column COL1 in TEST1 and TEST2 are 1 and 2, respectively. Alternatively, use the table's specific recycle station name to recover. To this end, first, you must identify the name of the recycle bin of the table, then execute: flashback table "bin $ 04lhcpnoanfgmaaaaaaanpw == $ 0" to before Drop rename to test2;
Flashback Table "BIN $ 04LHCPNQANFGMAAAAAAAAAAAAANPW == $ 0" to Before Drop Rename to Test1;
These statements will recover two versions of the deleted table.
caveat……
Cancel the deletion feature makes the table restores its original name, but the relevant objects such as index and triggers do not return the original name, which still uses the name of the recycle station. The sources (such as views and procedures) defined on the table are not recompiled and remain invalid. These original names must be manually obtained and applied to the flash back table.
The information is kept in the view called User_Recyclebin. Before the table is flashed back, 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 $ 04LHCPNianFGMAAAAAAAANPW == $ 0 in_RT_01 INDEX
BIN $ 04LHCPNGANFGMAAAAAAAAAAANPW == $ 0 Tr_RT Trigger After the table is flashed, the index and flip flops on the table RecycleTest will be named in the Object_name column. According to the above query, you can use the original name to rename the object, as shown below:
Alter Index "BIN $ 04LHCPNianFGMAAAAAAAAANPW == $ 0" rename to in_rt_01;
ALTER TRIGGER "BIN $ 04LHCPNGANFMAAAAAAAAANPW == $ 0" Rename to Tr_RT;
A payable exception is a bitmap index. When the bitmap index is deleted, they are not placed in the recycle bin - therefore cannot retrieve them. The constraint name cannot be retrieved from the view. They must be renamed from other sources.
Other uses of flashback table
Flashback Delete Table features are not limited to the removal operation of the recovery table. Similar to flashback queries, you can also use it to recover tables to different point points, replace the entire table using the "past" version of the table. For example, the following statement restores the table to the system change number (SCN) 2202666520.
Flashback Table Recycletest to SCN 2202666520;
This feature uses Oracle data pump technology to create different tables, using the Flashback feature to populate the data version at the SCN to the table, and replace the original table with a new table. To find out what extent, it is possible to flash back to the table, you can use the version control feature of Oracle Database 10g. (For more details, please refer to the first week of this series.) You can also specify a timestamp instead of the specified SCN in the flashback clause.
