Oracle数据库中delete表后commit后找回的方法

有些时候，不小心删除了一些需要的表，而且数据库不能停止，只能一直运行下去，这样的话很麻烦

下面介绍的方法就是删除表后通过时间戳后者scn找回删除的数据

模拟实验环境：

创建一个新表

SQL> create table www as select * from hr.employees;

Table created.

查看新表是否有数据

SQL> select count(*) from www;

COUNT(*)

----------

107

查看当前的时间，这里时间或者scn有一个就行。后面会有通过scn恢复的案例

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2017-11-13 17:34:45

查找时间戳之前是否有数据

SQL> select count(*) from www as of timestamp to_timestamp('2017-11-13 17:30:45','yyyy-mm-dd hh24:mi:ss');

select count(*) from www as of timestamp to_timestamp('2017-11-13 17:30:45','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

查询下这个时间段的数据是存在的

SQL> select count(*) from www as of timestamp to_timestamp('2017-11-13 17:34:45','yyyy-mm-dd hh24:mi:ss');

COUNT(*)

----------

107

删除表：

SQL> delete from www;

107 rows deleted.

SQL> commit;

Commit complete.

查看www表是没有数据的

SQL> select * from www;

no rows selected

当前时间查看也是没有数据了

SQL> select count(*) from www as of timestamp to_timestamp('2017-11-13 17:37:45','yyyy-mm-dd hh24:mi:ss');

COUNT(*)

----------

0

我们需要这样做，查看下删除之前的时间，根据删除之前的时间戳找到www表中的数据

SQL> select count(*) from www as of timestamp to_timestamp('2017-11-13 17:34:45','yyyy-mm-dd hh24:mi:ss');

COUNT(*)

----------

107

查看时间戳发现有数据存在，说明数据可以恢复

SQL> insert into www select * from www as of timestamp to_timestamp('2017-11-13 17:34:45','yyyy-mm-dd hh24:mi:ss');

107 rows created.

SQL> commit;

Commit complete.

数据恢复成功

SQL> select count(*) from www;

COUNT(*)

----------

107

同理，scn也是可以恢复的

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

3698087

SQL> create table www as select * from hr.employees;

Table created.

SQL> select count(*) from www;

COUNT(*)

----------

107

查看创建表之后的scn

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

3698135

SQL> delete from www;

107 rows deleted.

表中的数据已经没有了

SQL> select count(*) from www;

COUNT(*)

----------

0

SQL> commit;

Commit complete.

通过创建表之后的scn号来查询后，找到了相关数据

SQL> select count(*) from www as of scn 3698135;

COUNT(*)

----------

107

将数据插入到表中即可

SQL> insert into www select * from www as of scn 3698135;

107 rows created.

SQL> commit;

Commit complete.

完成恢复。

同理scn和时间戳可以互相转换 scn-->timestamp

SQL> select scn_to_timestamp(3698087) scn from dual;

SCN

---------------------------------------------------------------------------

13-NOV-17 05.43.11.000000000 PM

timestamp-->scn

SQL> select timestamp_to_scn(to_timestamp('2017-11-13 17:34:45','yyyy-mm-dd hh24-mi-ss')) scn from dual;

SCN

----------

3697660