Oracle 闪回表实验

我就是我 2022-08-10 12:56 140阅读 0赞

作业：闪回表实验

1.构造测试表flb\_test，数据不小于10000行；

[TEST\_USER1@PROD>create][TEST_USER1_PROD_create] table flb\_test(id number,dd date);

Table created.

[TEST\_USER1@PROD>begin][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][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][TEST_USER1_PROD_select] dbms\_flashback.get\_system\_change\_number from dual;

GET\_SYSTEM\_CHANGE\_NUMBER  
\------------------------  
                 1144357

3.查看该测试表block数目及大小M；

[TEST\_USER1@PROD>select][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][TEST_USER1_PROD_create] index ind\_flb on flb\_test(id,dd);

Index created.

5.查看该索引的叶子块的数目以及层数；

[TEST\_USER1@PROD>select][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][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][TEST_USER1_PROD_delete] from flb\_test where id<=5000;

5000 rows deleted.

[TEST\_USER1@PROD>commit][TEST_USER1_PROD_commit];

Commit complete.

[TEST\_USER1@PROD>select][TEST_USER1_PROD_select] count(\*) from flb\_test;

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

[TEST\_USER1@PROD>exec][TEST_USER1_PROD_exec] dbms\_stats.gather\_table\_stats('TEST\_USER1','FLB\_TEST');

PL/SQL procedure successfully completed.

[TEST\_USER1@PROD>exec][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][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][TEST_USER1_PROD_alter] table flb\_test enable row movement;

Table altered.

[TEST\_USER1@PROD>select][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][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][TEST_USER1_PROD_exec] dbms\_stats.gather\_table\_stats('TEST\_USER1','FLB\_TEST');

PL/SQL procedure successfully completed.

[TEST\_USER1@PROD>exec][TEST_USER1_PROD_exec] dbms\_stats.gather\_index\_stats('TEST\_USER1','IND\_FLB');

PL/SQL procedure successfully completed.  
    --收集表和索引的统计信息  
    --Oracle只是闪回表，所有的东西都原样保留，应重新收集统计信息

8.查看闪回结果，以及索引状态；

[TEST\_USER1@PROD>select][TEST_USER1_PROD_select] count(\*) from flb\_test;

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

[TEST\_USER1@PROD>select][TEST_USER1_PROD_select] INDEX\_NAME,STATUS ,BLEVEL,LEAF\_BLOCKS from dba\_indexes  
  2  where index\_name ='IND\_FLB';

[TEST_USER1_PROD_create]: mailto:TEST_USER1@PROD>create
[TEST_USER1_PROD_begin]: mailto:TEST_USER1@PROD>begin
[TEST_USER1_PROD_select]: mailto:TEST_USER1@PROD>select
[TEST_USER1_PROD_delete]: mailto:TEST_USER1@PROD>delete
[TEST_USER1_PROD_commit]: mailto:TEST_USER1@PROD>commit
[TEST_USER1_PROD_exec]: mailto:TEST_USER1@PROD>exec
[TEST_USER1_PROD_alter]: mailto:TEST_USER1@PROD>alter
[TEST_USER1_PROD_flashback]: mailto:TEST_USER1@PROD>flashback