Oracle中flush buffer cache和x$bh

news/2024/4/20 21:27:41/文章来源:https://blog.csdn.net/jnrjian/article/details/129133292

对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。 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: