oracle作业：闪回表实验 1.构造测试表flb_test，数据不小于10000行； test_user1@prodgt;create table flb_test(id number,dd d
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:29
test_user1@prod>select dbms_flashback.get_system_change_number from dual;
get_system_change_number
------------------------
1144357
3.查看该测试表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
--平衡树： 高度=层数+1
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
6.删除测试表中一半的记录数并提交；
test_user1@prod>delete from flb_test where id
5000 rows deleted.
test_user1@prod>commit;
commit complete.
test_user1@prod>select count(*) from flb_test;
count(*)
----------
5000
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.
--收集表和索引的统计信息
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 disabled
test_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 enabled
test_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(*)
----------
10000
test_user1@prod>select index_name,status ,blevel,leaf_blocks from dba_indexes
2 where index_name ='ind_flb';