Flashback TABLE 实验

sys用户

1、简单删除

SQL> flashbacktable scott.emp to before drop;

闪回完成。

SQL> selectindex_name from dba_indexes where table_name='TMP';

未选定行

SQL> selectindex_name from dba_indexes where table_name='EMP';

INDEX_NAME

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

BIN$cDoUigC2TnW6+9HHGhBJ8A==$0

SQL> alter index"BIN$cDoUigC2TnW6+9HHGhBJ8A==$0" rename to PK_EMP;

alter index"BIN$cDoUigC2TnW6+9HHGhBJ8A==$0" rename to PK_EMP

*

第 1 行出现错误:

ORA-01418: 指定的索引不存在

SQL> alter index"BIN$cDoUigC2TnW6+9HHGhBJ8A==$0" rename to scott.PK_EMP;

alter index"BIN$cDoUigC2TnW6+9HHGhBJ8A==$0" rename to scott.PK_EMP

                                                           *

第 1 行出现错误:

ORA-14047: ALTERTABLE|INDEX RENAME 不能与其它分区组合

SQL> alter indexscott."BIN$cDoUigC2TnW6+9HHGhBJ8A==$0" rename to PK_EMP;

索引已更改。

SQL> selectindex_name from dba_indexes where table_name='EMP';

INDEX_NAME

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

PK_EMP

SQL> selectconstraint_name from dba_constraints where table_name='EMP';

CONSTRAINT_NAME

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

BIN$vsV+9Qu+Rl2UYarsjC4irw==$1

SQL> alter tablescott.emp  rename  constraint"BIN$vsV+9Qu+Rl2UYarsjC4irw==$1" to PK_EMP;

表已更改。

SQL> selectconstraint_name from dba_constraints where table_name='EMP';

CONSTRAINT_NAME

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

PK_EMP

SQL>

2、恢复时有同名表

SQL> create tablescott.tmp as select *  fromscott.flash_tbl;

表已创建。

SQL> drop tablescott.tmp;

表已删除。

SQL> create tablescott.tmp as select *  fromscott.flash_tbl;

表已创建。

SQL> flashbacktable scott.tmp to before drop;

flashback tablescott.tmp to before drop

*

第 1 行出现错误:

ORA-38312:原始名称已被现有对象使用

SQL> flashbacktable scott.tmp to before drop rename to temp1;

闪回完成。

SQL> select *  from scott.temp1;

        ID VL

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

       201 A1

       202 B1

         7 PK_DEPT

         8 PK_DEPT

         9 PK_DEPT

        10 DEPT

        11 DEPT

        12 DEPT

        13 DEPT

        14 DEPT

        15 DEPT

        ID VL

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

       116 DEPT

       117 DEPT

       118 DEPT

       119 EMP

已选择15行。

SQL>

3、从多次删除同名表中恢复

SQL> drop tablescott.tmp;

表已删除。

SQL> create tablescott.tmp as select *  fromscott.flash_tbl;

表已创建。

SQL> drop tablescott.tmp;

表已删除。

SQL> create tablescott.tmp as select *  fromscott.flash_tbl;

表已创建。

SQL> drop tablescott.tmp;

表已删除。

SQL> selectobject_name,original_name,droptime from recyclebin;

未选定行

SQL> selectobject_name,original_name,droptime from dba.recyclebin;

selectobject_name,original_name,droptime from dba.recyclebin

                                                  *

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> selectobject_name,original_name,droptime from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$BoxixBy1TjW4YdBiedbp1g==$0TMP                             2011-07-29:23:50:46

BIN$AGeJA2WUQ42CTQLo5Nk7Ag==$0TMP                             2011-07-29:23:50:19

BIN$1d7kqYtrRSK8IZuFVL1c2g==$0TMP                             2011-07-29:23:50:42

BIN$/rDcN3ndTh2ngTVC+R1W/g==$0TMP                             2011-07-29:23:50:49

SQL> flashbacktable tmp to before drop;

flashback table tmpto before drop

*

第 1 行出现错误:

ORA-38305: 对象不在回收站中

SQL> flashbacktable scott.tmp to before drop;

闪回完成。

SQL> selectobject_name,original_name,droptime from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$BoxixBy1TjW4YdBiedbp1g==$0TMP                             2011-07-29:23:50:46

BIN$AGeJA2WUQ42CTQLo5Nk7Ag==$0TMP                             2011-07-29:23:50:19

BIN$1d7kqYtrRSK8IZuFVL1c2g==$0TMP                             2011-07-29:23:50:42

SQL> flashbacktable scott.tmp to before drop;

flashback tablescott.tmp to before drop

*

第 1 行出现错误:

ORA-38312:原始名称已被现有对象使用

SQL> flashbacktable scott.tmp to before drop rename to tmp1;

闪回完成。

SQL> selectobject_name,original_name,droptime from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$AGeJA2WUQ42CTQLo5Nk7Ag==$0TMP                             2011-07-29:23:50:19

BIN$1d7kqYtrRSK8IZuFVL1c2g==$0TMP                             2011-07-29:23:50:42

SQL> drop tablescott.tmp

  2  ;

表已删除。

SQL> selectobject_name,original_name,droptime from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$AGeJA2WUQ42CTQLo5Nk7Ag==$0TMP                             2011-07-29:23:50:19

BIN$1d7kqYtrRSK8IZuFVL1c2g==$0TMP                             2011-07-29:23:50:42

BIN$Sz887iS9TzOJtQIDh1YiIw==$0 TMP                             2011-07-29:23:56:06

SQL> flashbacktable scott.tmp to before drop;

闪回完成。

SQL> selectobject_name,original_name,droptime from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$AGeJA2WUQ42CTQLo5Nk7Ag==$0TMP                             2011-07-29:23:50:19

BIN$1d7kqYtrRSK8IZuFVL1c2g==$0TMP                             2011-07-29:23:50:42

SQL>

===========》刚删除的被最先恢复

可以指定恢复

SQL> flashbacktable scott."BIN$AGeJA2WUQ42CTQLo5Nk7Ag==$0" to before drop rename totmp2;

闪回完成。

SQL> selectobject_name,original_name,droptime from dba_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

BIN$1d7kqYtrRSK8IZuFVL1c2g==$0TMP                             2011-07-29:23:50:42

SQL>

4、从undo表空间中直接恢复误操作

SQL> selectdbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                 1023083

SQL> updatescott.flash_tbl set id=id+10 where id>10;

已更新11行。

SQL> insert intoscott.flash_tbl values(21,'z');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * fromscott.flash_tbl;

        ID VL

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

       211 A1

       212 B1

        21 z

         7 PK_DEPT

         8 PK_DEPT

         9 PK_DEPT

        10 DEPT

        21 DEPT

        22 DEPT

        23 DEPT

        24 DEPT

        ID VL

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

        25 DEPT

       126 DEPT

       127 DEPT

       128 DEPT

       129 EMP

已选择16行。

SQL> select *  from scott.flash_tbl as of scn  1023083;

        ID VL

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

       201 A1

       202 B1

         7 PK_DEPT

         8 PK_DEPT

         9 PK_DEPT

        10 DEPT

        11 DEPT

        12 DEPT

        13 DEPT

        14 DEPT

        15 DEPT

        ID VL

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

       116 DEPT

       117 DEPT

       118 DEPT

       119 EMP

已选择15行。

SQL> flashbacktable scott.flash_tbl to scn  1023083;

flashback tablescott.flash_tbl to scn  1023083

                      *

第 1 行出现错误:

ORA-08189:因为未启用行移动功能, 不能闪回表

SQL> selectrow_movement from dba_tables where table_name='A';

ROW_MOVE

--------

DISABLED

SQL> alter table scott.flash_tbl enable row movement;===》要启动行移动的功能

表已更改。

SQL> flashbacktable scott.flash_tbl to scn  1023083;

闪回完成。

SQL> select * fromscott.flash_tbl;

        ID VL

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

       201 A1

       202 B1

         7 PK_DEPT

         8 PK_DEPT

         9 PK_DEPT

        10 DEPT

        11 DEPT

        12 DEPT

        13 DEPT

        14 DEPT

        15 DEPT

        ID VL

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

       116 DEPT

       117 DEPT

       118 DEPT

       119 EMP

已选择15行。

SQL> alter table scott.tmp disable row movement;

表已更改。

SQL>

不能对sys用户下的表使用flashback

SQL> conn sys/ymhas sysdba;

已连接。

SQL> showparameter recyclebin

NAME                                 TYPE        VALUE

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

recyclebin                           string      ON

SQL> create tabletmp(id int);

表已创建。

SQL> drop tabletmp;

表已删除。

SQL> select *  from recyclebin ;

未选定行

SQL> select *  from user_recyclebin ;

未选定行

SQL> select *  from dba_recyclebin ;

OWNER                          OBJECT_NAME                    ORIGINAL_NAME

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

SCOTT                         BIN$3lUPDEw+RA+mIB2+Ot6WsQ==$0 TMP

SCOTT                         BIN$1d7kqYtrRSK8IZuFVL1c2g==$0 TMP

SCOTT                         BIN$7i2IgZdhSG+IWz5bDB+nZA==$0 YMH

SQL> flashbacktable tmp to before drop;

flashback table tmpto before drop

*

第 1 行出现错误:

ORA-38305: 对象不在回收站中