Using Flashback Drop and Managing the Recycle Bin

Using Flashback Drop and Managing the Recycle Bin

使用闪回删除和管理回收站

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACKTABLE statement is used to restore the table. Before discussing the use of theFLASHBACKTABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.

删除一个表，数据库不会立即被回收。数据库重命名这个表并且将表及相关对象放到回收站。当表被错误的删除，可以在一段时间内恢复。这个特性称为闪回删除，FLASHBACKTABLE语句用来恢复表。

This section contains the following topics:

    What Is the Recycle Bin?

             什么是回收站

    Viewing and Querying Objects in the Recycle Bin

             查看和查询回收站内的对象

    Purging Objects in the Recycle Bin

             清除回收站内的对象

    Restoring Tables from the Recycle Bin

             从回收站恢复表

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

回收站实际上是一个数据字典表，包含被删除对象的信息。被删除的表和相关的对象，例如索引、约束、嵌套表、类似的对象任然占用空间。它们计算在用户空间配额，直到从回收站清除或者由于表空间约束被数据库清除。

Each user can be thought of as having his own recycle bin, because, unless a user has theSYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

除了有SYSDBA权限的用户，每个用户有属于自己的回收站，用户访问回收站的对象是用户自己的对象。一个用户可以他的回收站内的对象使用下面的语句：

SELECT * FROM RECYCLEBIN;
 

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

    When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.

    When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.

    When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

删除表空间及它里面的内容，在表空间里的对象不会放置到回收站，数据库会清除回收站内位于这个表空间的对象记录。当删除表空间不包含内容，表空间另外是空的，数据库也会清除回收站内属于这个表空间的对象记录。同样的：

             当删除用户，用户的对象不会放到回收站并且清除回收站的对象

             当删除簇，簇的成员表不会放到回收站并且清除回收站的对象

             当删除类型，任何依靠的对象例如子类型不会放到回收站并且清除之前依靠的对象从回收站

 Object Naming in the Recycle Bin

回收站内的对象命名

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name. This could occur under the following circumstances:

当删除的表挪到回收站，表和相关的对象使用系统生成的名称。这样避免名称冲突。

    A user drops a table, re-creates it with the same name, then drops it again.

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

The renaming convention is as follows:

命名约定如下：

BIN$unique_id$version

where:

    unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases

    version is a version number assigned by the database

Enabling and Disabling the Recycle Bin

启用和禁用回收站

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. When the recycle bin is disabled, dropped tables and their dependent objects arenot placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

启用回收站，删除的对象和依赖对象放置到回收站。禁用回收站，删除的对象和依赖对象不会放置到回收站；他们刚刚被删除，你必须采用其他方式进行恢复(例如从备份中恢复)。

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

禁用回收站不会清除或者影响已经在回收站的对象。默认启用回收站。

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with anALTERSYSTEM statement.

通过改变参数recyclebin启用和禁用回收站。这个不是动态参数，当使用ALTERSYSTEM改变参数，需要重启数据库才能生效。

To disable the recycle bin:

禁用回收站：

    Issue one of the following statements:

    ALTER SESSION SET recyclebin = OFF;

    ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

    If you used ALTER SYSTEM, restart the database.

To enable the recycle bin:

启用回收站：

    Issue one of the following statements:

    ALTER SESSION SET recyclebin = ON;

    ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;

    If you used ALTER SYSTEM, restart the database.

Oracle Database provides two views for obtaining information about objects in the recycle bin:

oracle提供了获取回收站内对象信息的两个视图：

View	Description
USER_RECYCLEBIN	This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN	This view gives administrators visibility to all dropped objects in the recycle bin

One use for these views is to identify the name that the database has assigned to a dropped object, as shown in the following example:

使用视图查询删除对象的一个列子：

SELECT object_name, original_name FROM dba_recyclebin
   WHERE owner = 'HR';

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES

You can also view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN.

使用SHOW RECYCLEBIN.显示回收站的对象：

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES        BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE        2003-10-27:14:00:19

You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

查询回收站对象包含的内容，需要使用回收站标识的对象名称：

SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

Purging Objects in the Recycle Bin

清除回收站内的对象：

If you decide that you are never going to restore an item from the recycle bin, you can use thePURGE statement to remove the items and their associated objects from the recycle bin and release their storage space. You need the same privileges as if you were dropping the item.

如果已决定不恢复回收站的一个对象，可以使用PURGE清除它和相关对象。

When you use the PURGE statement to purge a table, you can use the name that the table is known by in the recycle bin or the original name of the table. The recycle bin name can be obtained from either theDBA_ orUSER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". The following hypothetical example purges the tablehr.int_admin_emp, which was renamed toBIN$jsleilx392mk2=293$0 when it was placed in the recycle bin:

使用PURGE语句清除表，可以使用表在回收站的名称或者这个表的原始名称。回收站里的名称可以通过DBA_ 或USER_RECYCLEBIN获取。下面假设的例子清除表hr.int_admin_emp，它在回收站中命名为BIN$jsleilx392mk2=293$0：

PURGE TABLE "BIN$jsleilx392mk2=293$0";

You can achieve the same result with the following statement:

你也可以使用下面的语句：

PURGE TABLE int_admin_emp;

You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified tablespace or only the tablespace objects belonging to a specified user, as shown in the following examples:

可以使用PURGE语句清除在回收站内的所有对象，这些对象属于一个指定的表空间或者仅仅表空间中属于一个指定的用户，如下面的例子：

PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;

Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:

用户可以清除回收站，释放空间：

PURGE RECYCLEBIN;
 

If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifyingDBA_RECYCLEBIN, instead ofRECYCLEBIN in the previous statement.

You can also use the PURGE statement to purge an index from the recycle bin or to purge from the recycle bin all objects in a specified tablespace.

如果你有SYSDBA权限，可以清空所有的回收站，用DBA_RECYCLEBIN代替前面语句中的RECYCLEBIN。
你也可以使用PURGE清除回收站内的索引或者清除回收站内指定表空间的所有对象。

Restoring Tables from the Recycle Bin

从回收站恢复表

Use the FLASHBACK TABLE ... TO BEFOREDROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. An optionalRENAME TO clause lets you rename the table as you recover it. The recycle bin name can be obtained from either theDBA_ orUSER_RECYCLEBIN view as shown in"Viewing and Querying Objects in the Recycle Bin". To use theFLASHBACKTABLE ...TO BEFOREDROP statement, you need the same privileges required to drop the table.

使用FLASHBACK TABLE ... TO BEFOREDROP从回收站恢复对象。你可以指定对象名称为回收站中的名称，也可以使用原始表名。RENAME TO子句可以恢复时重命名表。

The following example restores int_admin_emp table and assigns to it a new name:

下面的例子恢复表int_admin_emp并重命名：

FLASHBACK TABLE int_admin_emp TO BEFORE DROP
   RENAME TO int2_admin_emp;

The system-generated recycle bin name is very useful if you have dropped a table multiple times. For example, suppose you have three versions of theint2_admin_emp table in the recycle bin and you want to recover the second version. You can do this by issuing twoFLASHBACK TABLE statements, or you can query the recycle bin and then flashback to the appropriate system-generated name, as shown in the following example. Including the create time in the query can help you verify that you are restoring the correct table.

如果你多次删除同样的表，系统生成的回收站对象名称是非常有用的。例如，假设表int2_admin_emp在回收站有三个版本，你想恢复第二个版本。你可以通过两个FLASHBACK TABLE语句，或者可以查询回收站并闪回合适的系统生成的名称，像下面的列子。create time可以帮助你恢复正确的表。

SELECT object_name, original_name, createtime FROM recyclebin;    

OBJECT_NAME                    ORIGINAL_NAME   CREATETIME
------------------------------ --------------- -------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:21:05:52
BIN$yrMKlZaVMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:21:25:13
BIN$yrMKlZaQMhfgNAgAIMenRA==$0 INT2_ADMIN_EMP  2006-02-05:22:05:53

FLASHBACK TABLE "BIN$yrMKlZaVMhfgNAgAIMenRA==$0" TO BEFORE DROP;

Restoring Dependent Objects

恢复依赖对象

When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object's system-generated recycle bin namebefore you restore the table.

当从回收站恢复一个表，依赖对象例如索引不能获得原来的名字。他们仍然保持系统生成的回收站内的名称。你必须手动重命名依赖对象恢复他们原来的名字。

The following is an example of restoring the original names of some of the indexes of the dropped tableJOB_HISTORY, from theHR sample schema. The example assumes that you are logged in as theHR user.

下面是一个恢复索引原来名称的例子：

    After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

    SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

    OBJECT_NAME                    ORIGINAL_NAME             TYPE
    ------------------------------ ------------------------- --------
    BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX              INDEX
    BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX         INDEX
    BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX       INDEX
    BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK   INDEX
    BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY               TABLE

    Restore the table with the following command:

    FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

    Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

    SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';
     
    INDEX_NAME
    ------------------------------
    BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
    BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
    BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
    BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

    Restore the original names of the first two indexes as follows:

    ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
    ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

    Note that double quotes are required around the system-generated names.