doc

    Name:   bufcache.sql

    Author: Mark Gurry


The following script provides information on the status of all blocks in 
the buffer cache and lists how many of each object currently reside in
the buffer cache.

To create the view v$bh, which is used by this script, you must run    
the script $ORACLE_HOME/rdbms/admin/catparr.sql on UNIX machines. 
#

ttitle ' Current Buffer Cache Usage     '
select status, count(*) 
	from v$bh 
group by status;

Doc

To obtain the objects in the buffer cache, and the number of buffers 
each is using, you can run the following script. You have to re-run 
catparr.sql prior to re-running this script. (See details on catparr.sql 
in the chapter on Tuning Parallel Server). This script takes a while to run,
hence the question in the where clause, which is making sure that you 
need to run it. 
#

ttitle ' Breakdown of what is Currently Being Stored in the Buffer Cache'
select kind, name, status, count(*) 
	from v$cache
 where substr('&Look_inside_Buffer_Cache?',1,1) in ('y','Y')
 group by kind, name, status;