to find object in buffer pool
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: to find object in buffer pool

  1. #1
    Join Date
    Feb 2005
    Posts
    10

    to find object in buffer pool

    I want to keep the table in Buffer pool
    so i issue the following command.

    ALTER TABLE MI_FULL_PLAIN STORAGE (BUFFER_POOL KEEP);

    please help me with an SQL to find what tables are cached in the buffer_pool alone Whether the table is present or not.

    thnks

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    you can try this:
    select 'KEEP' POOL ,
    o.name,
    COUNT(obj#) BLOCKS
    from obj$ o ,
    x$bh x
    where o.dataobj# = x.obj
    and x.state != 0
    and o.owner# !=0
    and obj# >=
    (select lo_bnum
    from v$buffer_pool
    where name = 'KEEP' and buffers > 0)
    and obj# < =
    (select hi_bnum from v$buffer_pool where
    name = 'KEEP' and buffers > 0)
    group by 'KEEP', o.name
    /
    ---------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width