♣
题目部分
在Oracle中，什么是闪回表（Flashback TABLE）？
♣
答案部分
闪回表（Flashback TABLE）是将表恢复到过去的某个时间点或某个SCN值时的状态。对闪回表语句不能进行回滚，如果要闪回表，那么需要有对表的FLASHBACK对象权限或者FLASHBACK ANY TABLE系统权限。
代码语言：javascript复制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
下面是验证索引在FLASHBACK TABLE TO SCN中的情况：
代码语言：javascript复制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 >
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
