I'd like to check if a segment has really been uploaded in the buffer pool and the only way to check it, it seems to use this query under Oracle 19c:
select o.owner,o.object_name,count(blocks) bl
from sys.dba_objects o, sys.v_$bh b
where o.object_id = b.objid
and o.owner = 'TEST'
I executed the query:
set autotrace traceonly statistics;
select * from test.dummy;
I've got the following report: 79645 consistent gets - 12457 physical reads
I executed the query again and now I've got: 79101 consistent gets - 0 physical reads
Now I queried again the v_$bh view with the owner = 'TEST' but the segment 'dummy' is not present in the view. So it appears that blocks have been read from buffer pool but the v_$bh view has not been updated in agreement with sql trace statistics.
I did the same in Postgresql 13.x using pg_buffercache extension to query shared buffers and it works fine, is it possible to do the same under Oracle 19c ?