How do I find out what is in my Oracle buffer Cache?

First of all, you can try running this query:

SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS
FROM V$DB_CACHE_ADVICE
WHERE NAME = 'KEEP'
AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size')
AND ADVICE_STATUS = 'ON';

This many not return any rows depending upon what other initilisation parameters you have or have not set. You may find that there are rows in the V$DB_CACHE_ADVICE view, but with the ADVICE_STATUS column set to OFF. This means that the parameter DB_CACHE_ADVICE is set to either OFF or READY.

You can check by running this query:

SELECT *
FROM v$parameter
WHERE name LIKE 'db_cache%';

This parameter can be set to ON | OFF | READY
You can go from READY to ON, at the cost of CPU and memory overhead
Going from OFF to ON may cause error if done dynamically when the instance is up
Start DB with READY to allocate memory but not incur CPU overhead
Switch to ON for a set period of time - 1 day - then switch off. Check objects in pools

Below is an example of what you might like to know about what’s in your buffer cache.

Find the number of buffers in the instance:

SELECT value "total buffers"
FROM v$parameter
WHERE name = 'db_block_buffers';

Find out the object ID of the objects you are interested in (table or index)

SELECT data_object_id, object_type
FROM user_objects
WHERE object_name = '';

Find the number of buffers in the buffer cache for segment_name:

SELECT count(*) buffers
FROM x$bh
WHERE obj = ;

Calculate the ratio of buffers to total buffers, to obtain the percentage of the cache currently used by segment_name

%cache used by segment_name = buffers (step2) / total buffers (step3)

Note: This technique works only for a single segment; for a partitioned object, the query must be run for each partition.