Oracle 闪回表实验

Oracle作业：闪回表实验1.构造测试表flb_test，数据不小于10000行；TEST_USER1@PROD>create table flb_test（id number,dd date）;Table created.TEST_USER1@PROD>begin
2 for i in 1..10000
3 loop
4 insert into flb_test values （i,sysdate+i）;
5 end loop;
6 end;
7 /PL/SQL procedure successfully completed.exec dbms_stats.gather_table_stats（"TEST_USER1","FLB_TEST"）;
--收集统计信息
2.查询当前时间与scn号；TEST_USER1@PROD>select to_char（sysdate,"yyyy-mm-dd hh24:mi:ss"） from dual;TO_CHAR（SYSDATE,"YY
-------------------
2014-10-13 19:23:29TEST_USER1@PROD>select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER
------------------------
11443573.查看该测试表block数目及大小M；TEST_USER1@PROD>select SEGMENT_NAME,BYTES/1024/1024 size_m, BLOCKS from user_segments
2 where SEGMENT_NAME="FLB_TEST";SEGMENT_NAME SIZE_M BLOCKS
--------------- ---------- ----------
FLB_TEST .25 32
4.在这张表的第一和第二列上，创建一个复合索引ind_flb;TEST_USER1@PROD>create index ind_flb on flb_test（id,dd）;Index created.5.查看该索引的叶子块的数目以及层数；TEST_USER1@PROD>select INDEX_NAME,STATUS ,BLEVEL,LEAF_BLOCKS from dba_indexes
2 where index_name ="IND_FLB";INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------------ -------- ---------- -----------
IND_FLB VALID 1 33 --平衡树：高度=层数+1TEST_USER1@PROD>select SEGMENT_NAME,BYTES/1024/1024 size_m, BLOCKS from user_segments
2 where SEGMENT_NAME="FLB_TEST";SEGMENT_NAME SIZE_M BLOCKS
--------------- ---------- ----------
FLB_TEST .25 32
6.删除测试表中一半的记录数并提交；TEST_USER1@PROD>delete from flb_test where id<=5000;5000 rows deleted.TEST_USER1@PROD>commit;Commit complete.TEST_USER1@PROD>select count（*） from flb_test; COUNT（*）
----------
5000TEST_USER1@PROD>exec dbms_stats.gather_table_stats（"TEST_USER1","FLB_TEST"）;PL/SQL procedure successfully completed.TEST_USER1@PROD>exec dbms_stats.gather_index_stats（"TEST_USER1","IND_FLB"）;PL/SQL procedure successfully completed.
--收集表和索引的统计信息7.闪回fls_test到第二步查询到的时间点；TEST_USER1@PROD>select table_name ,row_movement from user_tables;TABLE_NAME ROW_MOVE
------------------------------ --------
SALARY ENABLED
SYS_TEMP_FBT DISABLED
FLB_TEST DISABLED
EMP DISABLEDTEST_USER1@PROD>alter table flb_test enable row movement;Table altered.TEST_USER1@PROD>select table_name ,row_movement from user_tables;TABLE_NAME ROW_MOVE
------------------------------ --------
EMP DISABLED
FLB_TEST ENABLED
SYS_TEMP_FBT DISABLED
SALARY ENABLEDTEST_USER1@PROD>flashback table flb_test to timestamp to_timestamp（"2014-10-13 19:23:29","yyyy-mm-dd hh24:mi:ss"）;Flashback complete.
TEST_USER1@PROD>exec dbms_stats.gather_table_stats（"TEST_USER1","FLB_TEST"）;PL/SQL procedure successfully completed.TEST_USER1@PROD>exec dbms_stats.gather_index_stats（"TEST_USER1","IND_FLB"）;PL/SQL procedure successfully completed.
--收集表和索引的统计信息
--Oracle只是闪回表，所有的东西都原样保留，应重新收集统计信息
8.查看闪回结果，以及索引状态；TEST_USER1@PROD>select count（*） from flb_test; COUNT（*）
----------
10000TEST_USER1@PROD>select INDEX_NAME,STATUS ,BLEVEL,LEAF_BLOCKS from dba_indexes
2 where index_name ="IND_FLB";INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------------ -------- ---------- -----------
IND_FLB VALID 1 33Oracle 11g Flashback Data Archive（闪回数据归档） http://www.linuxidc.com/Linux/2013-06/86696.htmOracle Flashback闪回机制 http://www.linuxidc.com/Linux/2013-05/84223.htmOracle Flashback database http://www.linuxidc.com/Linux/2013-05/84129.htmFlashback table快速恢复误删除的数据 http://www.linuxidc.com/Linux/2012-09/70988.htmOracle 备份恢复：Flashback闪回 http://www.linuxidc.com/Linux/2012-09/69958.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址