Oracle Flash Back Table experiment

Job: Flash Back Table experiment

1. Construct the test table flb_test, the data is not less than 10000 lines;

[email protected]>create table flb_test (ID number,dd date);

Table created.

[email protected]>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 ');
--Collect statistical information

2. Query the current time and SCN number;

[email protected]>select to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (sysdate, ' YY
-------------------
2014-10-13 19:23:29

[email protected]>select dbms_flashback.get_system_change_number from dual;

Get_system_change_number
------------------------
1144357

3. View the test table block number and size m;

[email protected]>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. On the first and second columns of this table, create a composite index IND_FLB;

[email protected]>create index ind_flb on flb_test (ID,DD);

Index created.

5. View the number of leaf blocks and the number of layers in the index;

[email protected]>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

--Balance Tree: height = number of layers +1

[email protected]>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. Delete half the number of records in the test table and submit;

[email protected]>delete from Flb_test where id<=5000;

The deleted rows.

[email protected]>commit;

Commit complete.

[email protected]>select count (*) from flb_test;

COUNT (*)
----------
5000

[email protected]>exec dbms_stats.gather_table_stats (' test_user1 ', ' flb_test ');

PL/SQL procedure successfully completed.

[email protected]>exec dbms_stats.gather_index_stats (' test_user1 ', ' ind_flb ');

PL/SQL procedure successfully completed.
--collecting statistics for tables and indexes

7. Flash back to the fls_test to the second step of the query to the point of time;

[email protected]>select table_name, row_movement from User_tables;

TABLE_NAME Row_move
------------------------------ --------
SALARY ENABLED
SYS_TEMP_FBT DISABLED
Flb_test DISABLED
EMP DISABLED

[email protected]>alter table flb_test enable row movement;

Table altered.

[email protected]>select table_name, row_movement from User_tables;

table_name                      row_move
--------------------------------------
emp                              DISABLED
flb_test                        ENABLED
sys_temp_fbt                    DISABLED
salary                          ENABLED

[email protected]>flashback table Flb_test to timestamp to_timestamp (' 2014-10-13 19:23:29 ', ' yyyy-mm-dd Hh24:mi:ss ');

Flashback complete.

[email protected]>exec dbms_stats.gather_table_stats (' test_user1 ', ' flb_test ');

PL/SQL procedure successfully completed.

[email protected]>exec dbms_stats.gather_index_stats (' test_user1 ', ' ind_flb ');

PL/SQL procedure successfully completed.
--collecting statistics for tables and indexes
--oracle is just a flashback table, everything is kept intact and statistics should be collected again

8. Review the flashback results and the status of the index;

[email protected]>select count (*) from flb_test;

COUNT (*)
----------
10000

[email protected]>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 Flash Back Table experiment