标签：name buffer cache SYS orcl150 file oracle where select
文档课题：oracle判断数据块是否存在内存buffer cache中.
数据库：oracle 11.2.0.4
1、概念理论
通过V$BH视图可以查询.V$BH保存着Buffer Cache中每个BLOCK的信息.如T1表在数据文件1中，第一个BLOCK是103001，则通过如下SQL可查看此BLOCK有没有在Buffer Cache中，若返回值大于0，说明该块在Buffer Cache中.
select count(*) from v$bh where file#=1 and block#=103001;
2、实验测试
2.1、建测试表
SYS@orcl150> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--创建用于演示的datafile.
SYS@orcl150> create tablespace tbs_tmp datafile '/u01/app/oracle/oradata/orcl150/tbs_tmp01.dbf' size 10m autoextend on;
Tablespace created.
SYS@orcl150> grant select on dba_objects to scott;
Grant succeeded.
SYS@orcl150> alter user scott account unlock;
User altered.
SYS@orcl150> alter user scott identified by tiger;
User altered.
SYS@orcl150> conn scott/tiger;
Connected.
--基于新的数据文件创建tb_tmp表.
SCOTT@orcl150> create table tb_tmp tablespace tbs_tmp as select * from dba_objects;
Table created.
SYS@orcl150> col file_name for a60
SYS@orcl150> select file_id,file_name from dba_data_files where tablespace_name='TBS_TMP';
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
8 /u01/app/oracle/oradata/orcl150/tbs_tmp01.dbf
2.2、执行测试
--查表tb_tmp的信息，如文件信息、头部块、总块数.
SYS@orcl150> col segment_name for a15
SYS@orcl150> select segment_name,header_file,header_block,blocks from dba_segments where segment_name='TB_TMP' and owner='SCOTT';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------- ----------- ------------ ----------
TB_TMP 8 130 1280
SYS@orcl150> select count(*) from v$bh where file#=8 and block#=130;
COUNT(*)
----------
1
说明：如上所示，数据文件号8，块号130的数据块在buffer cache中.
参考资料：https://blog.51cto.com/lhrbest/2707833
标签：name,
buffer,
cache,
SYS,
orcl150,
file,
oracle,
where,
select
From： https://blog.51cto.com/u_12991611/6044943