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,droptime from dba.recyclebin;

selectobject_name,droptime from dba.recyclebin

????????????????????????????????????????????????? *

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> selectobject_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,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,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,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,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,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: 对象不在回收站中