Very Computer

Greetings.

I am currently using the following query to find the
objects that are cached in the block buffer pool.

select a.status, b.object_name, count(*) total
from v$bh a, dba_objects b
where a.objd = b.object_id(+)
group by a.status, b.object_name;

It works fine. But, I'd really like to include the pool
in the output (keep, recycle, default). However, there
doesn't seem to be a field in v$bh (or even x$bh) that
corresponds to the pool.

Does anyone know how to get this information? I assume
I need to join with another table. Which one is it?

--- Rich

Quote:

> Greetings.

> I am currently using the following query to find the
> objects that are cached in the block buffer pool.

> select a.status, b.object_name, count(*) total
> from v$bh a, dba_objects b
> where a.objd = b.object_id(+)
> group by a.status, b.object_name;

> It works fine. But, I'd really like to include the pool
> in the output (keep, recycle, default). However, there
> doesn't seem to be a field in v$bh (or even x$bh) that
> corresponds to the pool.

> Does anyone know how to get this information? I assume
> I need to join with another table. Which one is it?

> --- Rich

select kcbwbpd.bp_name, o.name, count(*) BLOCKS
from sys.x$kcbwds kcbwds, sys.x$kcbwbpd kcbwbpd , sys.x$bh bh, sys.obj$ o
where kcbwds.set_id >= kcbwbpd.bp_lo_sid
and kcbwds.set_id <= kcbwbpd.bp_hi_sid
and kcbwbpd.bp_size != 0
and bh.indx between start_buf# and end_buf#
and o.dataobj# = bh.obj
and bh.state !=0
and o.owner# !=0
and bp_name = 'KEEP'
group by kcbwbpd.bp_name, o.name

thanks

Quote:

> Greetings.

> I am currently using the following query to find the
> objects that are cached in the block buffer pool.

> select a.status, b.object_name, count(*) total
> from v$bh a, dba_objects b
> where a.objd = b.object_id(+)
> group by a.status, b.object_name;

> It works fine. But, I'd really like to include the pool
> in the output (keep, recycle, default). However, there
> doesn't seem to be a field in v$bh (or even x$bh) that
> corresponds to the pool.

> Does anyone know how to get this information? I assume
> I need to join with another table. Which one is it?

> --- Rich

Thanks,
Sunil.

Quote:> I know this is out of context but can some one please explain the difference
> between the X$ and V$ views

There is a V$ view that actually contains the SQL used to build all the
other V$ views from the X$ data.  Can't remember its name but it's
obvious if you do

select view_name from dba_views
where owner = 'SYS' and view_name like 'V_$%';

connected as user SYS.  It's one of the many views (and many other
mechanisms) used by folks like Steve Adams and Jonathan to figure out how
things work inside Oracle.  

HTH
--
Cheers
Nuno Souto