Whats in the Buffer Cache

View what is in the buffer cache

SELECT o.owner, o.object_type, substr(o.object_name,1,10)
objname, b.objd, b.status, count(b.objd)
FROM v$bh b, dba_objects o
WHERE b.objd = o.data_object_id
-- AND o.owner not in ('SYS','SYSTEM','SYSMAN')
-- and o.owner = 'USERNAME'
GROUP BY o.owner, o.object_type, o.object_name,
b.objd, b.status

we can un-comment the following lines to see only the schema blocks we are interested in.
AND o.owner not in ('SYS','SYSTEM','SYSMAN')
and o.owner = 'USERNAME'


We can flush the buffer cache to clean out the buffer cache

alter system flush buffer_cache;

******************************************
keywords: buffer cache
******************************************
rdbms version: 10g 11g
******************************************