Oracle 閃回表實驗

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: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.

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';

INDEX_NAME                    STATUS      BLEVEL LEAF_BLOCKS
------------------------------ -------- ---------- -----------
IND_FLB                        VALID            1          33

Oracle 11g Flashback Data Archive(閃回資料歸檔)

Oracle Flashback閃回機制

Oracle Flashback database

Flashback table快速恢複誤刪除的資料

Oracle 備份恢複：Flashback閃回