ORACLE 10g 中恢复已删除的表

SQL> create table tt2 as select * from employee;

Table created.

SQL> drop table tt2;

Table dropped.

SQL> select * from tt2;
select * from tt2
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table tt2 to before drop;

Flashback complete.

SQL> select count(1) from tt2;

COUNT(1)
----------
32

如果在表删除后,又新建了这个表,恢复时可以重命名,如下:

 

SQL> drop table tt2;

Table dropped.

SQL> flashback table tt2 to before drop
2 rename to tt2_old;

Flashback complete.

SQL> select count(1) from tt2_old;

COUNT(1)
----------
32

如果这个表删除了不止一次,我想恢复某一次的删除,怎么办?
查询recyclebin视图,或者show recyclebin,然后带名字恢复.
SQL> drop table tt2_old;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TT2_OLD BIN$SOz1cXPCADLgQwoKCiEAMg==$0 TABLE 2008-03-21:13:51:49
SQL>
SQL> create table tt2_old as select * from job;

Table created.

SQL> drop table tt2_old;

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TT2_OLD BIN$SOz1cXPDADLgQwoKCiEAMg==$0 TABLE 2008-03-21:13:52:40
TT2_OLD BIN$SOz1cXPCADLgQwoKCiEAMg==$0 TABLE 2008-03-21:13:51:49

SQL> flashback table "BIN$SOz1cXPCADLgQwoKCiEAMg==$0" to before drop;

Flashback complete.

SQL> desc tt2_old
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(15)
MIDDLE_INITIAL VARCHAR2(1)
JOB_ID NUMBER(3)
MANAGER_ID NUMBER(4)
HIRE_DATE DATE
SALARY NUMBER(7,2)
COMMISSION NUMBER(7,2)
DEPARTMENT_ID NUMBER(2)

可以看到上述结果不是job表的结构,而是第一次时的结构
使用限制:
1 只有非系统表空间,而且是本地管理的表空间
2 表上相关对象被保留,除了以下内容:
bitmap join indexes, referential integrity constraints (foreign key constraints), and
materialized view logs
3 索引只在删除表时被留,显式地删除索引不被放入回收站.