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

实验环境：
1.操作系统

[oracle@oracle ~]$ uname -a
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
[oracle@oracle ~]$ lsb_release -a
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
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago

2.数据库版本

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

实验过程：

SYS@proc> drop table test purge;
Table dropped.
SYS@proc> create table test as select * from dba_objects;
Table created.
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;
FILE# DBABLK STATE
---------- ---------- ----------
1 93952 1
SYS@proc> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@proc> startup;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 159385624 bytes
Database Buffers 356515840 bytes
Redo Buffers 3780608 bytes
Database mounted.
Database opened.
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;
no rows selected
SYS@proc> select count(*) from test;
COUNT(*)
----------
86991
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;
FILE# DBABLK STATE
---------- ---------- ----------
1 93952 1
1 93952 3
1 93952 3
1 94668 1
...省略部分内容...
FILE# DBABLK STATE
---------- ---------- ----------
1 97609 1
1 97743 1
1 97744 1
1 97745 1
318 rows selected.
SYS@proc> alter system flush buffer_cache;
System altered.
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;
FILE# DBABLK STATE
---------- ---------- ----------
1 93952 0
1 93952 0
1 93952 0
1 94668 0
...省略部分内容...
FILE# DBABLK STATE
---------- ---------- ----------
1 97609 0
1 97654 0
1 97738 0
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博客

Oracle中flush buffer cache和x$bh

最新文章