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;
5000 rows deleted.
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';
INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------------ -------- ---------- -----------
IND_FLB VALID 1 33
版权声明:本文博主原创文章,博客,未经同意不得转载。
相关文章
- ORACLE新参数MAX_IDLE_TIME和MAX_IDLE_BLOCKING_TIME简介
- Oracle数据库:oracle数据表格dmp,sql,pde格式导入与导出,视图、序列、索引等对象的导出,oracle完结,后续开启mysql的学习
- ORACLE中dba,user,v$等开头的常用表和视图
- 服务案例|AI算法在Oracle指标异常检查、故障预测之牛刀小试
- Oracle不足与MySQL优势
- oracle用NOT EXISTS替代NOT IN
- oracle国家字符集
- oracle 19c 升级job 没有同步的解决办法
- Oracle——listener数据库监听 lsnrctl
- [Oracle]System 表空间的文件丢失
- Oracle rownum
- Greenplum中与Oracle类似的排序函数实现
- oracle 使用 in 关键字查询且集合数量大小大于1000