【DB笔试面试812】在Oracle中，什么是闪回表（Flashback TABLE）？

LHR@orclasm > CREATE TABLE T_FT_20170617_LHR (A NUMBER,B NUMBER);

Table created.

LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(1,1);

1 row created.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                67902120

LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(2,2);

1 row created.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > SELECT * FROM T_FT_20170617_LHR;

         A          B
---------- ----------
         1          1
         2          2

LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                67902215

LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902120;----闪回表的时候，需要对表执行ROW MOVMENT
FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902120
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


LHR@orclasm > ALTER TABLE T_FT_20170617_LHR ENABLE ROW MOVEMENT;

Table altered.

LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902120;

Flashback complete.

LHR@orclasm > SELECT * FROM T_FT_20170617_LHR;

         A          B
---------- ----------
         1          1

LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902215;---闪回到最后的位置

Flashback complete.

LHR@orclasm > SELECT * FROM T_FT_20170617_LHR;

         A          B
---------- ----------
         1          1
         2          2

LHR@orclasm > DROP TABLE T_FT_20170617_LHR;

Table dropped.

LHR@orclasm > CREATE TABLE T_FT_20170617_LHR (A NUMBER,B NUMBER);

Table created.

LHR@orclasm >  INSERT INTO T_FT_20170617_LHR VALUES(1,1);

1 row created.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                67902871

LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(2,2);

1 row created.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > CREATE INDEX IDX_T_LHR ON T_FT_20170617_LHR(A);

Index created.

LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;---该SCN表中存在索引

GET_SYSTEM_CHANGE_NUMBER
------------------------
                67902969

LHR@orclasm > INSERT INTO T_FT_20170617_LHR VALUES(3,3);

1 row created.

LHR@orclasm > COMMIT;

Commit complete.

LHR@orclasm > SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                67903002

LHR@orclasm > SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T_FT_20170617_LHR';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_LHR                      VALID

LHR@orclasm > ALTER TABLE T_FT_20170617_LHR ENABLE ROW MOVEMENT;

Table altered.

LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902871;

Flashback complete.

LHR@orclasm > SELECT * FROM T_FT_20170617_LHR;--恢复到没有创建索引之前的SCN

         A          B
---------- ----------
         1          1

LHR@orclasm > SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T_FT_20170617_LHR';--此时索引仍然存在

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_LHR                      VALID

LHR@orclasm > FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67903002;--闪回到最后的SCN

Flashback complete.

LHR@orclasm > SELECT * FROM T_FT_20170617_LHR;

         A          B
---------- ----------
         1          1
         2          2
         3          3

LHR@orclasm >  DROP INDEX IDX_T_LHR;

Index dropped.

LHR@orclasm >  FLASHBACK TABLE T_FT_20170617_LHR TO SCN 67902969;--闪回到创建索引的SCN ，但是经过闪回后索引已经不再存在了

Flashback complete.

LHR@orclasm > SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T_FT_20170617_LHR';

no rows selected

LHR@orclasm >