Oracle中flush buffer cache和x$bh

2023-02-23 02:11| 来源: 网络整理| 查看: 265

对oracle数据库执行alter system flush buffer_cache;指令发现，x$bh表中已经存在的信息并不会被清除。实验环境： 1.操作系统 [oracle@oracle ~]$ uname -aLinux 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 -aLSB 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-noarchDistributor ID: RedHatEnterpriseServerDescription: Red Hat Enterprise Linux Server release 6.5 (Santiago)Release: 6.5Codename: 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 1SYS@proc> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SYS@proc> startup;ORACLE instance started.Total System Global Area 521936896 bytesFixed Size 2254824 bytesVariable Size 159385624 bytesDatabase Buffers 356515840 bytesRedo Buffers 3780608 bytesDatabase 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 selectedSYS@proc> select count(*) from test; COUNT(*)---------- 86991SYS@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 1318 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。 index rebuild 后都会变化。 X$BH 使用data_object_id 实验结论： oracle数据库执行alter system flush buffer_cache; 只能清除数据缓存，并不能清除BH信息。

并不是不清理，而是标记为0 进行释放

1 170333 0 1 170333 0 1 170333 1 1 170334 1 1 170334 0 1 170335 0 1 170335 1 1 170335 0 1 170336 0 1 170336 1

4424 rows selected.

sys@RPTTEST>

其他资料： 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

【本文地址】

公司简介

联系我们