Oracle中flush buffer cache和x$bh

实验出发点：
今天做关于参数db_file_multiblock_read_count实验的时候，详情见http://blog.itpub.net/30174570/viewspace-2140241/，对oracle数据库执行alter system flush buffer_cache;指令发现，x$bh表中已经存在的信息并不会被清除。

实验环境：
1.操作系统

1. [oracle@oracle ~]$ uname -a
   
2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
   
3. [oracle@oracle ~]$ lsb_release -a
   
4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
   
5. Distributor ID: RedHatEnterpriseServer
   
6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
   
7. Release:        6.5
   
8. Codename:       Santiago

2.数据库版本

1. SYS@proc> select * from v$version where rownum=1;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

实验过程：

1. SYS@proc> drop table test purge;
   
2. 
   
3. Table dropped.
   
4. 
   
5. SYS@proc> create table test as select * from dba_objects;
   
6. 
   
7. Table created.
   
8. 
   
9. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;
   
10. 
    
11.      FILE#     DBABLK      STATE
    
12. ---------- ---------- ----------
    
13.          1      93952          1
    
14. 
    
15. SYS@proc> shutdown immediate;
    
16. Database closed.
    
17. Database dismounted.
    
18. ORACLE instance shut down.
    
19. SYS@proc> startup;
    
20. ORACLE instance started.
    
21. 
    
22. Total System Global Area 521936896 bytes
    
23. Fixed Size                 2254824 bytes
    
24. Variable Size            159385624 bytes
    
25. Database Buffers         356515840 bytes
    
26. Redo Buffers               3780608 bytes
    
27. Database mounted.
    
28. Database opened.
    
29. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;
    
30. 
    
31. no rows selected
    
32. 
    
33. SYS@proc> select count(*) from test;
    
34. 
    
35.   COUNT(*)
    
36. ----------
    
37.      86991
    
38. 
    
39. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;
40. 
    
41.      FILE#     DBABLK      STATE
    
42. ---------- ---------- ----------
    
43.          1      93952          1
    
44.          1      93952          3
    
45.          1      93952          3
    
46.          1      94668          1
    
47. ...省略部分内容...
    
48.      FILE#     DBABLK      STATE
    
49. ---------- ---------- ----------
    
50.          1      97609          1
    
51.          1      97743          1
    
52.          1      97744          1
    
53.          1      97745          1
    
54. 
    
55. 318 rows selected.
    
56. SYS@proc> alter system flush buffer_cache;
    
57. 
    
58. System altered.
    
59. 
    
60. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') order by dbablk;
    
61. 
    
62.      FILE#     DBABLK      STATE
    
63. ---------- ---------- ----------
    
64.          1      93952          0
    
65.          1      93952          0
    
66.          1      93952          0
    
67.          1      94668          0
    
68. ...省略部分内容...
    
69.      FILE#     DBABLK      STATE
    
70. ---------- ---------- ----------
    
71.          1      97609          0
    
72.          1      97654          0
    
73.          1      97738          0
    
74.          1      97739          0
    

需要注意的地方：
select file # , dbablk , state from x$bh where obj = ( select data_object_id from dba_objects where owner = 'SYS' and object_name = 'TEST' ) order by dbablk ; 这里的data_object_id在对表做move后不能替换成object_id。
具体原因参见我的另一篇博文： http://blog.itpub.net/30174570/viewspace-2140058/

实验结论：
oracle数据库执行alter system flush buffer_cache; 只能清除数据缓存，并不能清除BH信息。

其他资料：
The possible values of X$BH.STATE are:

0, FREE, no valid block image

1, XCUR, a current mode block, exclusive to this instance

2, SCUR, a current mode block, shared with other instances

3, CR,   a consistent read (stale) block image

4, READ, buffer is reserved for a block being read from disk

5, MREC, a block in media recovery mode

6, IREC, a block in instance (crash) recovery mode

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30174570/viewspace-2140062/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30174570/viewspace-2140062/