flashback drop 误删除的表后的恢复及对回收站中表的操作

长平狐 发布于 2013/09/17 15:10
阅读 95
收藏 0

1.误删除表,使用flashback drop功能找回

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
SQL> select * from test;
         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         0.
SQL> show parameter recyclebin;    -验证回收站是否打开--如未打开不能使用闪回DROP--11G默认打开
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL> show recyclebin;
SQL> drop table test;
Table dropped.
查询回收站,删除的表在里面,并被系统自己命令--BIN$开头一长串---从ORIGINAL NAME 列可以看到表的原名是test
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE   DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$387tHPzSqO/gQKjA3QEnzg==$0 TABLE        2013-06-23:17:29:01
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------

BIN$387tHPzSqO/gQKjA3QEnzg==$0 TABLE

使用回收站中的BIN$的表名也能查询到表中数据

SQL> select * from "BIN$387tHPzSqO/gQKjA3QEnzg==$0";
         A
----------
         1
         2
         3
         4
         5
         6
         7
         8

         0

执行闪回删除表的命令并查询数据是否完整

SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from "BIN$387tHPzSqO/gQKjA3QEnzg==$0";
select * from "BIN$387tHPzSqO/gQKjA3QEnzg==$0"
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
SQL> select * from test;
         A
----------
         1
         2
         3
         4
         5
         6
         7
         8

         0

2.闪回删除并将表重命名

BYS@bys1>select * from test5;
        ID NAME
---------- ----------
         2 b       
BYS@bys1>drop table test5;
Table dropped.

BYS@bys1>flashback table test5 to before drop rename to test6;
Flashback complete.
BYS@bys1>select * from test6;
        ID NAME
---------- ----------
         2 b

3.回收站内查看删除的表,单个删除回收站里的表、全部清空回收站。

SQL> create table test2 as select * fromtest1;

表已创建。

SQL> insert into test2values('00002','baishan2');

已创建 1 行。

SQL> select * from test2;

ID   NAME

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

00001 baishan

00002 baishan2

SQL> show recyclebin

SQL> drop table test1;

表已删除。

删除表后查看回收站里,可以看到删除的表名,及删除时间

SQL> showrecyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST1            BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0TABLE        2013-02-10:12:17:11

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE

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

BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0 TABLE

TEST                           TABLE

TEST2                          TABLE

SQL> create table test3 as select * fromtest2;

表已创建。

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE

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

BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0 TABLE

TEST                           TABLE

TEST2                          TABLE

TEST3                          TABLE

DROP时使用purge参数删除的表被直接删除,不存放在回收站。

SQL> drop table test2 purge;

表已删除。

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST1            BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0TABLE        2013-02-10:12:17:11

清空回收站

SQL> purge  recyclebin;

回收站已清空。 


SQL> drop table test2;

表已删除。

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST2            BIN$UabVC8wiRx+GdsN4VT5bRw==$0TABLE        2013-02-12:17:10:46

SQL> drop table test3;

表已删除。

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST2            BIN$UabVC8wiRx+GdsN4VT5bRw==$0TABLE        2013-02-12:17:10:46

TEST3            BIN$n8CZgrwoSRO16TFB/dXOcA==$0TABLE        2013-02-12:17:11:35

清除回收站中的test2表。

SQL> purge tabletest2;

表已清除。

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST3            BIN$n8CZgrwoSRO16TFB/dXOcA==$0TABLE        2013-02-12:17:11:35

SQL> flashback table test3 to before drop;

闪回完成。

SQL> show recyclebin

SQL> select count(*) from test3;

 COUNT(*)

----------

        2


原文链接:http://blog.csdn.net/q947817003/article/details/11580431
加载中
返回顶部
顶部