oracle10g还原删除数据,ORACLE 10g 中回复已删除的表

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 索引只在删除表时被留,显式地删除索引不被放入回收站.