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