Oracle flash back table experiment

Oracle flash back table experiment

Oracle job: flashback table experiment

1. Construct the flb_test table, with no less than 10000 rows of data;

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 ');
-- Collect statistics

2. query the current time and scn number;

TEST_USER1 @ PROD> select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'yy
-------------------
19:23:29

TEST_USER1 @ PROD> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1144357

3. Check the number and size of blocks in the test table;

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. Create a composite index ind_flb on the first and second columns of the table;

TEST_USER1 @ PROD> create index ind_flb on flb_test (id, dd );

Index created.

5. view the number of leaf blocks and the number of layers of the index;

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

-- Balance Tree: Height = Layers + 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. Delete and submit half of the records in the test table;

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.
-- Collect statistics of tables and Indexes

7. Flash fls_test to the time point found in step 2;

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 ('2017-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.
-- Collect statistics of tables and Indexes
-- Oracle only flashes back to the table. Everything is retained as is and statistics should be collected again.

8. view the flash back result and index status;

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 (flash back Data archiving)

Oracle Flashback flash back Mechanism

Oracle Flashback database

Flashback table quick recovery of accidentally deleted data

Oracle backup recovery: Flashback flash back