ITPub博客

首页 > 数据库 > Oracle > flashback drop

flashback drop

原创 Oracle 作者:wenjunheyixiao 时间:2016-01-21 15:31:05 0 删除 编辑

 

 

 

1、每个用户自己建立的表空间上都有一个回收站

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE

LXTB1                          TABLE

BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE

 

SQL> drop table lxtb1;

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

EMP1             BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:47:17

LXTB1            BIN$tBi/ze9iTkTgQAB/AQBpLA==$0 TABLE        2011-12-15:11:04:56

LXTB5            BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:46:01

11:09:45 SQL> desc "BIN$tBi/ze9iTkTgQAB/AQBpLA==$0";

 Name                                                              Null?    Type

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

 ID                                                                         NUMBER(38)

2、闪回

11:11:07 SQL> flashback table lxtb1 to before drop;

 

Flashback complete.

 

11:11:19 SQL> show recyclebin;    //查看回收站里面没有了

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

EMP1             BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:47:17

LXTB5            BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:46:01

11:11:29 SQL> select * from scott.lxtb1;

       100

       101

       102

 

3 rows selected.

 

purge

1、不放回回收站,直接删除

11:14:25 SQL> drop table lxtb1 purge;

 

Table dropped.

11:15:34 SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

EMP1             BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:47:17

LXTB5            BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:46:01

 

闪回的表和现在有表重名

11:18:41 SQL> select * from lxtb3;

         1

 

1 row selected.

 

11:18:45 SQL> drop table lxtb3;

 

Table dropped.

 

11:22:29 SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

EMP1             BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:47:17

LXTB3            BIN$tBi/ze9jTkTgQAB/AQBpLA==$0 TABLE        2011-12-15:11:19:47

LXTB5            BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:46:01

 

11:19:47 SQL> create table lxtb3(id int) tablespace test;

 

Table created.

11:20:29 SQL> select * from tab;

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE

BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE

LXTB1                          TABLE

BIN$tBi/ze9jTkTgQAB/AQBpLA==$0 TABLE

LXTB3                          TABLE

 

11:20:05 SQL> flashback table lxtb3 to before drop;

flashback table lxtb3 to before drop

*

ERROR at line 1:

ORA-38312: original name is used by an existing object

 

恢复直接改名

11:22:34 SQL> flashback table lxtb3 to before drop rename to lxtb3bak;

 

Flashback complete.

11:24:54 SQL> select * from lxtb3bak;

         1

 

1 row selected.

 

清除回收站

1:25:36 SQL> drop table lxtb3bak;

 

Table dropped.

 

11:27:01 SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

EMP1             BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:47:17

LXTB3BAK         BIN$tBi/ze9kTkTgQAB/AQBpLA==$0 TABLE        2011-12-15:11:27:01

LXTB5            BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:46:01

11:27:08 SQL> purge table lxtb3bak;  //从回收站表把清除

 

Table purged.

 

11:29:04 SQL> select * from tab;

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE

BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE

LXTB1                          TABLE

LXTB3                          TABLE

 

8 rows selected.

 

11:29:10 SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

EMP1             BIN$tAcLIS4s9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:47:17

LXTB5            BIN$tAcLIS4r9qbgQAB/AQA2mw==$0 TABLE        2011-12-14:12:46:01

11:29:18 SQL> purge recyclebin;

 

Recyclebin purged.

 

11:29:32 SQL> select * from tab;

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

LXTB1                          TABLE

LXTB3                          TABLE

 

6 rows selected.

 

11:29:40 SQL> show recyclebin;

 

 

注意:system表空间没有回收站,删除表后不能闪回

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30606702/viewspace-1980239/,如需转载,请注明出处,否则将追究法律责任。

上一篇: RMAN 维护
下一篇: 配置ASM
请登录后发表评论 登录
全部评论

注册时间:2015-12-02

  • 博文量
    117
  • 访问量
    159519