Select table_name, buffer_pool from user_tables
      WHERE Buffer_pool = 'KEEP'

        select * from v$db_object_cache t Where t.OWNER = 'PSLE1'
      AND T.TYPE = 'TABLE’

To Query all the the Tables which are Cached Query the View DBA_TABLES

select 'KEEP' POOL, o.name, count(buf#) BLOCKS
      fromobj$ o, x$bh x
      whereo.dataobj# = x.obj
      andx.state !=0
      ando.owner# !=0
      andbuf# >= (select lo_bnum from v$buffer_pool where name='KEEP'
      and buffers > 0)
      andbuf# <= (select hi_bnum from v$buffer_pool where name='KEEP'
      and buffers > 0)
      group by 'KEEP',o.name
      union all
      select 'DEFAULT' POOL, o.name, count(buf#) BLOCKS
      fromobj$ o, x$bh x
      whereo.dataobj# = x.obj
      andx.state !=0
      ando.owner# !=0
      andbuf# >= (select lo_bnum from v$buffer_pool where name='DEFAULT'
      and buffers > 0)
      andbuf# <= (select hi_bnum from v$buffer_pool where name='DEFAULT'
      and buffers > 0)
      group by 'DEFAULT',o.name
      union all
      select 'RECYCLE' POOL, o.name, count(buf#) BLOCKS
      fromobj$ o, x$bh x
      whereo.dataobj# = x.obj
      andbuf# >= (select lo_bnum from v$buffer_pool where name='RECYCLE'
      and buffers > 0)
      andx.state !=0
      ando.owner# !=0
      andbuf# <= (select hi_bnum from v$buffer_pool where name='RECYCLE'
      and buffers > 0)
      group by 'RECYCLE',o.name;

Keep the Frequently Accessed Master Table in the Buffer_Pool.
     For Keeping the Tables in Buffer Pool Use the Following Commands

     alter tableschema.tablename STORAGE (BUFFER_POOL KEEP);

Return to : Oracle Database, SQL, Application, Programming Tips