Oracle Database 10g: Deleting tables

Oracle
With the flash-back table feature in Oracle Database 10g, you can easily recover a table that was accidentally deleted

Here's what happens when it doesn't happen: the user deletes a very important table--of course accidentally--and needs to be recovered as quickly 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 the past, but it cannot flash back to DDL operations, such as deleting a table. The only way to recover is to use a point-in-time restore of the tablespace in another database, and then recreate the table in the current database using export/import or other methods. This process requires a lot of work and valuable time for DBAs, not to mention the use of another database for cloning.

Use the Flash Back table feature in Oracle Database 10g, which makes the recovery of the deleted table as simple as executing several statements. Let's see how the feature works.

Delete that watch!

First, let's look at the tables in the current pattern.



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 note that a new table bin$04lhcpndanfgmaaaaaanpw==$0 appears. That's what happened: the deleted table Recycletest not completely disappeared, 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 indexes or triggers are defined on the table, they are also renamed, using the same naming conventions as the table. Any related sources, such as procedures, are invalidated; the triggers and indexes of the original table are placed on the renamed table bin$04lhcpndanfgmaaaaaanpw==$0, preserving the complete object structure of the deleted table.


Tables and their related objects are placed in a logical container called the Recycle Bin, similar to the Recycle Bin in your PC. However, the objects are not removed from the table space they were originally in, and they still occupy the space there. The Recycle Bin is simply a logical structure that lists the directories of the deleted objects. Use the following command at the Sql*plus prompt to view its contents (you need to use Sql*plus 10.1来 to do this):


Sql> Show recyclebinoriginal 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 shows the new name in the Recycle Bin, which is the same name as the new table we saw after the deletion. (Note: The exact name may vary depending on the platform.) To recover the table, all you have 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 recovered without difficulty. If you look at the Recycle Bin now, it will be empty.

Remember, putting a table in the Recycle Bin does not free up space in the original tablespace. 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;


This command does not rename the table to the name in the Recycle Bin, but instead permanently deletes the table, just like the version before 10g.

Manage Recycle Bin

What happens when the deleted object takes up all the space if the table is not actually deleted in the process-and therefore no table space is freed?

The answer is simple: this will not 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, it can be said that the tablespace is under "Space pressure". At this point, the object is automatically purged from the Recycle Bin in a first-in, first-out manner. Related objects, such as indexes, are deleted before the table is deleted.



Similarly, space 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 run out of the portion of the table space that it allocates. In this case, Oracle automatically clears the object belonging to that user in the tablespace.

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 particular table named TEST, you can perform


PURGE TABLE TEST;


Or use the name in its Recycle Bin:


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


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


Purge index in_test1_01;


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

Sometimes it may be useful to purge at a higher level. For example, you might want to clear all objects in the Recycle Bin for tablespace USERS. Can perform:


PURGE tablespace USERS;


You may want to empty the Recycle Bin for only specific users in the tablespace. In a data warehouse type environment, a user creates and deletes many temporary tables, which can be useful at this point. You can change the above command to restrict the removal of only 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;


You can see that the Recycle Bin can be managed in a number of different ways to meet specific needs.

Table versioning and Flashback features

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 are performing a flashback operation on table TEST, what should the value of column COL1 be? 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. So the value of column COL1 is 3, not 1.

You can also retrieve other versions of the deleted table at this time. However, the presence of table TEST does not allow this to occur. You have two choices: 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, restoring the second version to TEST2. The values of columns COL1 in TEST1 and TEST2 will be 1 and 2, respectively. Alternatively, use a specific Recycle Bin name for the table to recover. To do this, first identify the Recycle Bin name 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 restore 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 Recycle Bin name. Sources that are defined on a table, such as views and procedures, are not recompiled and remain in an invalid state. These original names must be manually obtained 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 the table is flashed back.


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$04lhcpnianfgmaaaaaanpw==$0 in_rt_01 indexbin$ 04lhcpnganfgmaaaaaanpw==$0 Tr_rt TRIGGER


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


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


A notable exception is the bitmap index. When you delete bitmap indexes, they are not placed in the Recycle Bin-so 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 Flash-back Delete table feature is not limited to recovering table deletions. Like a flashback query, you can also use it to restore a table to a different point in time, replacing the entire table with a "past" version of the table. For example, the following statement restores a 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 Flashback feature to populate the table with the data version at the SCN, and then replaces the original table with the new table. To determine the extent to which a table can be flashed back, you can use the Oracle Database 10g version control feature. (For more details, see the 1th Week of this series.) You can also specify a timestamp in the flashback clause rather than the specified SCN.