Buffer Cache Content

If you want to know which objects are in the buffer cache

set pages 999
set lines 131
ttitle 'Contents of Data Buffers'
column owner  heading "Owner" format a10
column objname heading "Object|Name" format a25
column subobjname heading "Subobject|Name" format a25
column objtype heading "Object|Type" format a10
column bufferblocks heading "Blocks in|Buffer" format 999,999,999
column totalblocks heading "Total|Blocks" format 999,999,999
column bufferpercent heading "Percentage|in Buffer" format 999.99
column memkb heading "Memory|in KB" format 999,999,999
column blockkb heading "Block|Size|KB" format 99
select
 s.owner owner,
 object_name objname,
 subobject_name subobjname,
 substr(object_type,1,10) objtype,
 ts.block_size / 1024 blockkb,
 buffer.blocks blocks,
 s.blocks totalblocks,
 (buffer.blocks * ts.block_size / 1024) memkb,
 (buffer.blocks/decode(s.blocks, 0, .001, s.blocks))*100 bufferpercent
from
 (select o.owner, o.object_name, o.subobject_name,
         o.object_type object_type, count(*) blocks
  from dba_objects o, v$bh bh
  where o.object_id = bh.objd and o.owner not in ('SYS','SYSTEM')
  group by o.owner, o.object_name, o.subobject_name, o.object_type) buffer,
  dba_segments s,
 dba_tablespaces ts
where s.tablespace_name = ts.tablespace_name
  and s.owner = buffer.owner
  and s.segment_name = buffer.object_name
  and s.SEGMENT_TYPE = buffer.object_type
  and (s.PARTITION_NAME = buffer.subobject_name or buffer.subobject_name is null)
order by bufferpercent desc;

which will give you a nicely formatted report

                         Sub           Block Number                          
       Object            object Object  Size     of   Total Memory Percentage
Owner  Name              Name   Type      KB Blocks  Blocks  in KB  in Buffer
------ ----------------- ------ ------ ----- ------ ------- ------ ----------
SYSADM PSMENUDEFN               TABLE      4     16      16     64     100.00
SYSADM PS_LOCATION_TBL          TABLE      4    160     160    640     100.00
SYSADM PSROLEDEFN               TABLE      4     16      16     64     100.00
SYSADM PS_GENL_DEDUCTION        TABLE      4  5,591   5,632 22,364      99.27
SYSADM PS_JOBCODE_TBL           TABLE      4  3,864   4,096 15,456      94.34
SYSADM PS2POSITION_DATA         INDEX      4  2,162   2,304  8,648      93.84
SYSADM PS_PSMENUDEFNLANG        INDEX      4     15      16     60      93.75
SYSADM PS_COMPANY_TBL           TABLE      4     42      48    168      87.50
SYSADM PS0LOCATION_TBL          INDEX      4     41      48    164      85.42
  .      .                        .        .      .      .      .         .
  .      .                        .        .      .      .      .         .
  .      .                        .        .      .      .      .         .
SYSADM PS_PHYS_EXAM_LANG        TABLE      4      1   1,280      4        .08
SYSADM PS_PHYS_EXAM_LANG        INDEX      4      1   1,792      4        .06
SYSADM PSSQLTEXTDEFN            TABLE      4      2   4,096      8        .05
SYSADM PS_PSAPMSGARCHPH         INDEX      4      1   5,632      4        .02
SYSADM PS_PSAPMSGARCHTMP        INDEX      4      1   5,632      4        .02
SYSADM PSAPMSGPUBDATA           TABLE      4     51 394,240    204        .01
SYSADM PS_PSAPMSGARCHPD         INDEX      4      1   7,936      4        .01
SYSADM PSAPMSGARCHPD            TABLE      4      1 568,576      4        .00