Oracle Flashback Drop: Undo a DROP TABLE Operation

7.4 Oracle Flashback Drop: Undoa DROP TABLE Operation

Oracle Flashback Drop reverses theeffects of a DROP TABLE operation. It can be used to recover afterthe accidental drop of a table. Flashback Drop is substantially faster thanother recovery mechanisms that can be used in this situation, such aspoint-in-time recovery, and does not lead to any loss of recent transactions ordowntime.

     OracleFlashback Drop 技术可以撤销 DROP TABLE操作所造成的影响。 可以用来执行表的意外删除回复。在恢复表的误删除时，与基于其他恢复机制的技术相比，FLASHBACK DROP是相当快的，并且不会造成最近sh

When you drop a table, the databasedoes not immediately remove the space associated with the table. Instead, thetable is renamed and, along with any associated objects, it is placed inthe Recycle Bin of the database. The Flashback Drop operationrecovers the table from the recycle bin.

     当我们删除一张表时，数据库不会立即清除分配给该表的空间。而是将表和与其关联的对象重命名病存放到数据库的回收站中。flashback drop技术利用回收站讲删除的表对象进行回收恢复。

To understand how to use OracleFlashback Drop, you must also understand how the recycle bin works, and how toaccess and manage its contents.

     在使用flashback drop之前，我们必须首先明白回收站是如何工作的，通过何种方式来访问和管理回收站中的内容。

This section covers the followingtopics:

     本部分包括如下内容：

• What is the Recycle Bin?
• How Tables and Other Objects Are Placed in the Recycle Bin
• Naming Convention for Objects in the Recycle Bin
• Viewing and Querying Objects in the Recycle Bin
• Recycle Bin Capacity and Space Pressure
• Purging Objects from the Recycle Bin

7.4.1 What is the Recycle Bin?

The recycle bin is a logical container for all dropped tablesand their dependent objects. When a table is dropped, the database will storethe table, along with its dependent objects in the recycle bin so that they canbe recovered later. Dependent objects which are stored in the recycle bininclude indexes, constraints, triggers, nested tables, LOB segments and LOBindex segments.

回收站是一个本地容器，用来存放被删除的表和依赖与该表的对象。当一个表被删除，数据库会保留该表及依赖与该表的对象在回收站中，从而使我们可以在以后的时间点可以恢复该表，存放在回收站中的依赖对象包括：索引、约束、触发器、嵌套表、lob段和lob索引段。

7.4.2 How Tables and Other Objects Are Placed inthe Recycle Bin

Tables are placed in the recycle bin along with their dependentobjects whenever a DROP TABLE statement is executed. Forexample, this statement places the EMPLOYEE_DEMO table, along withany indexes, constraints, or other dependent objects listed previously, in therecycle bin:

当我们执行一个droptable语句时，该表和相应的依赖对象被存放到回收站。例如，下面的语句会将EMPLOYEE_DEMO表和依赖与该表的索引、约束及其他依赖对象放到回收站中。

SQL> DROP TABLE EMPLOYEE_DEMO;
Table Dropped

The table and its dependent objects will remain in therecycle bin until they are purged from the recycle bin.You can explicitly purge a table or other object from the recycle bin with theSQL*Plus PURGE statement, as described in "PurgingObjects from the Recycle Bin". If you aresure that you will not want to recover a table later, you can drop itimmediately and permanently, instead of placing it in the recycle bin, by usingthe PURGE option of the DROP TABLE statement, as shown inthis example:

回收站中的表以及依赖与该表的对象会一直保存到他们被从回收站清除。我们可以显示地直接使用purge语句清空回收站中的内容。如果我们确定被删除的表不需要恢复，那么我们可以drop table的purge选项直接永久性的删除该表而不是将该表放入回收站。如下所示：

DROP TABLE employee_demo PURGE;

Even if you do not purge objects from the recycle bin, thedatabase purges objects from the recycle bin to meet tablespace spaceconstraints. See "RecycleBin Capacity and Space Pressure" formore details.

即便我们没有将回收站中的对象清空出去，数据库也会将这些对象清空以便释放表空间压力。

Recycle bin objects are not counted as used space. If you querythe space views to obtain the amount of free space in the database, objects inthe recycle bin are counted as free space.

回收站战友的空间不会被标记为”已使用“。如果我们查询有关数据库空闲空间的视图，那么回收站中的对象所占用的空间是被标记为空闲空间的。

Dropped objects still appear in theviews USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX.A new column, DROPPED, is set to YES for these objects. You canuse the DROPPED column in queries against these views to view onlyobjects that are not dropped.

被删除到回收站的表依然可以在 USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX等视图中查询到，只是其dropped列被标记为YES。

To view only objects in the recycle bin, usethe USER_RECYCLEBIN and DBA_RECYCLEBIN views, describedlater in this chapter.

如果我们需要查看回收站中包含的对象，可以使用USER_RECYCLEBIN和DBA_RECYCLEBIN视图。下面的章节将会详细描述。

7.4.3 Naming Convention for Objects in theRecycle Bin

When a table and its dependent objects are moved tothe recycle bin, they are assigned unique names, to avoid name conflictsthat may arise in the following circumstances:

当数据表和依赖与该表的对象被移动到回收站后，他们会把分配唯一的名称，以避免在如下情况下，发生名称冲突

• A user drops a table, creates another with the same name, then drops the second table.

用户删除了一个表，创建了另一张同名的表，然后又将第二张表删除

• Two users have tables with the same name, and both users drop their tables.

两个用户创建了相同名称的表，并且两个用户都删除了表

The assigned names are globally unique and are used to identifythe objects while they are in the recycle bin. Object names are formed asfollows:

被分配的表的名称是全局唯一的，以便唯一标识这些在回收站的对象。对象名称是按照如下格式进行命名的

BIN$$globalUID$version

where:

• globalUID is a globally unique, 24 character long identifier generated for the object.

globalUID是全局唯一的，为每个对象生成一个24字符长度的标识符

• version is a version number assigned by the database

         version是数据库生成的一个版本数字

The recycle bin name of an object is always 30 characters long.

整个回收站对象的名称长度始终为30个字符

Note that the globalUID usedin the recycle bin name is not readily correlated with any externally visiblepiece of information about the object or the database.

globalUID与数据库或者数据对象的信息是不存在可读性关联的 。

7.4.4 Enabling and Disabling the Recycle Bin

The recycle bin is enabled by default. The initializationparameter RECYCLEBIN can be used to explicitly enable or disable therecycle bin.

默认情况下，回收站功能是被开启的。初始化参数RECYCLEBIN可以被用来显示的开启或者关闭回收站。

To enable the recycle bin , set the valueof RECYCLEBIN to ON.To disable the recycle bin, set the valueof RECYCLEBIN to OFF.

If you use a parameter file (PFILE) with your database, you canspecify the value of RECYCLEBIN in the parameter file, as in thisexample:

要开启回收站，设置RECECLEBIN为ON，要关闭回收站，设置RECYCLEBIN为off。如果使用pfile参数文件，那么可以在参数文件中按照如下方式设置RECYCLEBIN参数

# turn off recycle bin
RECYCLEBIN=OFF

This value applies to all database sessions.

在参数文件中设置该参数是应用到所有数据库会话的。

To specify recycle bin behavior for your own database session, youcan use an ALTER SESSION statement in SQL*Plus to change thevalue of the RECYCLEBIN parameter for your sessions. For example,this command disables the recycle bin for your database session:

如果想要为我们单独的会话设置回收站功能，可以使用alter session语句在设置某sesson的recyclebin参数。例如，如下的命令会讲当前我们的数据库会话的回收站功能关闭。

ALTER SESSION SET RECYCLEBIN=OFF;

Objects you drop during this session are no longer placed in therecycle bin, until you re-enable the recycle binusing ALTER SESSION SET RECYCLEBIN=ON. However, other userscontinue to be protected by the recycle bin, and in future sessions the valuereverts to the current default for the entire database.

You can also use an ALTER SYSTEM statement inSQL*Plus to change the value of the RECYCLEBIN parameter for theentire database. For example:

ALTER SYSTEM SET RECYCLEBIN=OFF;

This disables the recycle bin for all sessions, unless a userspecifically enables the recycle bin for their sessionusing ALTER SESSION SET RECYCLEBIN=ON.

Note:

Objects already in the recycle bin are not affected by enabling ordisabling the recycle binusing ALTER SYSTEM or ALTER SESSION.

已经在回收站中的对象是不会手alter system 和alter session语句影响的

7.4.5 Viewing and Querying Objects in the RecycleBin

To view the contents of the recycle bin, use the SQL*Pluscommand SHOW RECYCLEBIN.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                   TYPE         DROP TIME
---------------- --------------------------------- -------------------------------
EMPLOYEE_DEMO   BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE        2005-04-11:17:08:54

The ORIGINAL NAME column shows the orignal name of theobject, while the RECYCLEBIN NAME column shows the name of the objectas it exists in the recycle bin. Use the RECYCLEBIN NAME when issuingqueries against tables in the recycle bin.

The database also provices two views for obtaining informationabout objects in the recycle bin:

数据为我们提供了如下两个视图来查询回收站中的对象

View	Description
USER_RECYCLEBIN	Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN	Lets administrators see all dropped objects in the recycle bin

This example uses the views to determine the original names ofdropped objects:

SQL> SELECT object_name as recycle_name,original_name, type
     FROM recyclebin;

RECYCLE_NAME                      ORIGINAL_NAME         TYPE
-------------------------------- --------------------- ----------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 EMPLOYEE_DEMO         TABLE
BIN$JKS983293M1dsab4gsz/I249==$0 I_EMP_DEMO            INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0 LOB_EMP_DEMO           LOB
BIN$JKJ399SLKnaslkJSLK330SIK==$0 LOB_I_EMP_DEMO         LOB INDEX

You can query objects that are in the recycle bin, just as you canquery other objects, if these three conditions are met:

如果我们具有如下的权限，那么我们可以查询回收站中表的内容

• You must have the FLASHBACK privilege.
• You must have the privileges that were required to perform queries against the object before it was placed in the recycle bin.
• You must use the recycle bin name of the object in your query, rather than the object's original name.

This example shows the required syntax:

SQL> SELECT * FROM"BIN$KSD8DB9L345KLA==$0";

(Note the use of quotes due to the special characters in therecycle bin name.)

You can also use Oracle Flashback Query on tables in the recyclebin (again, assuming that you have the privileges described previously).

7.4.6 Recycle Bin Capacity and Space Pressure

There is no fixed amount of space preallocated for the recyclebin. Therefore, there is no guaranteed minimum amount of time during which adropped object will remain in the recycle bin.

The rules that govern how long an object is retained in therecycle bin and how and when space is reclaimed are explained in this section.

7.4.6.1 Understanding Space Pressure

Dropped objects are kept in the recycle bin until such time as nonew extents can be allocated in the tablespace to which the objects belongwithout growing the tablespace. This condition is referred to as space pressure. Space pressure can also arisedue to user quotas defined for a particular tablespace. A tablespace may havefree space, but the user may have exhausted his or her quota on it.

Oracle never automatically reclaims space or overwrites objects inthe recycle bin unless forced to do so in response to space pressure.

在用户的空间限额将要超出或者表空间空间不足时，oracle会使用回收站占用的空间。

7.4.6.2 How the Database Responds to SpacePressure

When space pressure arises, the database selects objects forautomatic purging from the recycle bin. Objects are selected for purging on afirst-in, first-out basis, that is, the first objects dropped are the firstselected for purging.

oracle清除回收站中对象是按照先入先出的原则进行的

Actual purging of objects is done only as needed to meet ongoingspace pressure, that is, the databases purges the minimum possible number ofobjects selected for purging to meet immediate needs for space. This policyserves two purposes:

• It minimizes the performance penalty on transactions that encounter space pressure, by not performing more purge operations than are required;
• It maximizes the length of time objects remain in the recycle bin, by leaving them there until space is needed.

Dependent objects such as indexes on a table are selected forpurging before the associated table (or other required segment).

清除回收站对象时，oracle会首先清除依赖于某表的索引然后才会清除该表

If space pressure is due to an individual user's quota on atablespace being exhausted, the recycle bin purges objects belonging to thetablespace which count against that user's space quotas.

For AUTO EXTEND-able tablespaces, objects are purged from therecycle bin to reclaim space before datafiles are extended.

对于自动扩展的表空间，oracle会在表空间扩展之前，回收回收站以释放空间

7.4.6.3 Recycle Bin Objects and Segments

The recycle bin operates at the object level, in terms of tables,indexes, and so on. An object may have multiple segments associated with it,such as partitioned tables, partitioned indexes, lob segments, nested tables,and so on. Because the database reclaims only the segments needed toimmediately satisfy space pressure, it can happen that some but not allsegments of an object are reclaimed. When this happens, any segments of theobject not reclaimed immediately are marked as temporary segments. Thesetemporary segments are the first candidates to be reclaimed the next time spacepressure arises.

如果一张表包含多个段（如分区表），那么当oracle回收其中一个段后，如果其他段不再需要回收，那么oracle会将这些段设置为临时段（此时该表是不可用闪回删除的），当下次需要释放空间时，oracle会首先释放这些段的空间。

In such a case, the partially-reclaimed object can no longer beremoved from the recycle bin with Flashback Drop. (For example, if onepartition of a partitioned table is reclaimed, the table can no longer be theobject of a Flashback Drop.)

7.4.7 Performing Flashback Drop on Tables in theRecycle Bin

Usethe FLASHBACK TABLE ... TO BEFORE DROP statementto recover objects from the recycle bin. You can specify either the name of thetable in the recycle bin or the original table name. This can be obtainedfrom either the DBA_RECYCLEBIN orUSER_RECYCLEBIN view as shownin "Viewingand Querying Objects in the Recycle Bin". Tousethe FLASHBACK TABLE ... TO BEFORE DROP statement,you need the same privileges you need to drop the table.

The following example restoresthe BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 table, changes its name backto hr.int_admin_emp, and purges its entry from the recycle bin:

FLASHBACK TABLE"BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

Note the use of quotes, due to the possibility of specialcharacters appearing in the recycle bin object names.You can also use thetable's original name in the Flashback Drop operation:

FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

You can assign a new name to the restored table by specifyingthe RENAME TO clause. For example:

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0"TO BEFORE DROP
     RENAME TO hr.int2_admin_emp;

7.4.7.1 Flashback Drop of Multiple Objects Withthe Same Original Name

You can create, and then drop, several objects with the sameoriginal name, and they will all be stored in the recycle bin. For example,consider these SQL statements:

CREATE TABLE EMP ( ...columns); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...columns ); # EMP version 3
DROP TABLE EMP;

In such a case, each table EMP is assigned a unique namein the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TOBEFORE DROP statement with the original name of the table, as shown inthis example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name isretrieved from the recycle bin, with its original name. You can retrieve it andassign it a new name using a RENAME TO clause. The following exampleshows the retrieval from the recycle bin of all threedropped EMP tables from the previous example, with each assigned anew name:

FLASHBACK TABLE EMPTO BEFORE DROP RENAME TO EMP_VERSION_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;

Because using the original namein FLASHBACK TABLE... TO BEFORE DROP refers tothe most recently dropped table with that name, the last table dropped is thefirst one to be retrieved.

如果我们删除了多个相同名称的表，那么flashback table语句默认会删除最后删除的表，除非我们直到了唯一表明

You can also retrieve any table you want from the recycle bin,regardless of any collisions among original names, by using the table's uniquerecycle bin name.

7.4.8 Purging Objects from the Recycle Bin

The PURGE command is used to permanently purge objectsfrom the recycle bin. Once purged, objects can no longer be retrieved from thebin using Flashback Drop.

There are a number of forms of the PURGE statement,depending on exactly which objects you want to purge from the recycle bin.

See Also:

Oracle DatabaseSQL Reference for more information on the PURGE statement

7.4.8.1 PURGE TABLE: Purging a Table andDependent Objects

The PURGE TABLE command purges an individual table andall of its dependent objects from the recycle bin. This example shows thesyntax, using the table's original name:

PURGE TABLE EMP;

You can also use the recycle bin name of an object with PURGETABLE:

PURGE TABLE"BIN$KSD8DB9L345KLA==$0";

If you have created and dropped multiple tables with the sameorignal name, then when you use the PURGE TABLE statement the firsttable dropped will be the one to be purged.

如果我们创建了多个相同名称的表，那么purge语句默认时清除最早删除的表

Note:

The behavior in this case is the opposite of the behaviorof FLASHBACK TABLE... TO BEFORE DROP, where using the original name of thetable retrieves the most recently dropped version from the recycle bin.

For example, consider the following series of CREATETABLE and DROP TABLE statements:

CREATE TABLE EMP;  # version 1 of the table
DROP TABLE EMP;     # version 1dropped
CREATE TABLE EMP;   # version 2 of thetable
DROP TABLE EMP;     # version 2dropped
CREATE TABLE EMP;   # version 3 of thetable
DROP TABLE EMP;     # version 3 dropped

There are now three EMP tables in the recycle bin. Ifyou execute PURGE TABLE EMP several times, the effect is as describedhere:

PURGE TABLE EMP;   # version 1 of the table is purged
PURGE TABLE EMP;    # version 2 of thetable is purged
PURGE TABLE EMP;    # version 3 of thetable is purged

7.4.8.2 PURGE INDEX: Freeing Space in the RecycleBin

You can use PURGE INDEX to purge just an index for atable, while keeping the base table in the recycle bin. The syntax for purgingan index is as follows:

PURGE INDEX"BIN$GTE72KJ22H9==$0";

By purging indexes from the recycle bin, you can reduce the chanceof space pressure, so that dropped tables can remain in the recycle bin longer.If you retrieve a table from the recycle bin using Flashback Drop, you canrebuild the indexes after you retrieve the table.

7.4.8.3 PURGE TABLESPACE: Purging All DroppedObjects from a Tablespace

You can use the PURGE TABLESPACE command to purge alldropped tables and other dependent objects from a specific tablespace. Thesyntax is as follows:

PURGE TABLESPACE hr;

You can also purge only objects from a tablespace belonging to aspecific user, using the following form of the command:

PURGE TABLESPACE hr USER scott;

7.4.8.4 PURGE RECYCLEBIN: Purging All Objects ina User's Recycle Bin

The PURGE RECYCLEBIN command purges the contents of therecycle bin for the currently logged-in user.

PURGE RECYCLEBIN;

It purges all tables and their dependent objects for this user,along with any other indexes owned by this user but not on tables owned by theuser.

7.4.8.5 PURGE DBA_RECYCLEBIN: Purging All RecycleBin Objects

If you have the SYSDBA privilege, then you can purge all objectsfrom the recycle bin, regardless of which user owns the objects, using thiscommand:

PURGE DBA_RECYCLEBIN;

7.4.8.6 Dropping a Tablespace, Cluster, User orType and the Recycle Bin

When a tablespace is dropped including its contents, the objectsin the tablespace are dropped immediately, and not placed in the recycle bin.Any objects in the recycle bin from the dropped tablespace are purged from therecycle bin.

当我们删除一个表空间时（including contents），表空间中的所有对象都将删除而不会放入回收站，原先在回收站中的该表空间对象也会清除。即便我们不使用including content,已经在回收站中的对象也会被清除。

If all objects from a tablespace have been placed in the recyclebin, then dropping the tablespace causes the objects to be purged, even if youdo not use the INCLUDING CONTENTS clause with DROP TABLESPACE.

When a user is dropped, any objects belonging to the user that arenot in the recycle bin are dropped immediately, not placed in the recycle bin.Any objects in the recycle bin that belonged to the user are purged from therecycle bin.

当我们删除一个用户时，该用户在回收站中的对象会被清除。

When you drop a cluster, all tables in the cluster are purged.When you drop a user-defined data type, all objects directly or indirectlydependent upon that type are purged.

当我们删除一个cluster时，所有cluster中的对象会被直接清除，而不放入回收站，如果我们删除了一个用户自定义数据类型，那么所以依赖于该数据类型的对象会被从回收站清除。

7.4.9 Privileges and Security for Flashback Drop

This section summariezes the system privileges required for theoperations related to Flashback Drop and the recycle bin.

• DROP
  Any user with drop privileges over the object can drop the object, placing it in the recycle bin.
• FLASHBACK TABLE... TO BEFORE DROP
  Privileges are tied to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop to retrieve the dropped object from the recycle bin.

任何具有删除某表权限的用户都可以从回收站闪回该表

• PURGE
  Privileges are tied to the DROP privileges. Any user having DROP TABLE or DROP ANY TABLE privileges can purge the objects from the recycle bin.

任何具有删除某表权限的用户都可以从回收站清空该表

• SELECT for objects in the Recycle Bin
  Users must have SELECT and FLASHBACK privileges over an object in the recycle bin to be able to query the object in the recycle bin. Any users who had the SELECT privilege over an object before it was dropped continue to have the SELECTprivilege over the object in the recycle bin.Users must have FLASHBACK privilege to query any object in the recycle bin, because these are objects from a past state of the database.

用户必须具有flashback权限才可以查看回收站中表的内容。

7.4.10 Limitations and Restrictions on FlashbackDrop

• The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.

回收站功能仅对本地管理的非系统表空间对象有效，如果依赖于该表的对象如所以不在本地管理的非系统表空间，这些表依然可以放到回收站，但是其依赖对象如索引不会放到回收站。

• There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
• While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.

不可用对回收站中的对象使用DML和DDL语句

• You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.

我们可以对回收站中的表对象使用闪回查询，但是只可以使用回收站中的表名，而不可以使用表的原始名字

• A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.
  It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
• Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.

出于安全上的考虑，具有FAG和VPD功能的表，是不会受到回收站功能保护的。

• Partitioned index-organized tables are not protected by the recycle bin.

索引组织表不会受到回收站功能的保护

• The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.

回收站不会保护参考索引，因此我们闪回删除表后，需要重建指向该表的参考索引