cached_blocks

> I get the cache_blocks.sql from this site. While I was executing this
> scripts I found a mistake.
>
> select
> e.owner||'.'||e.segment_name segment_name,
> sum(cur) cur_buffers,
> sum(cr) cr_buffers
> from
> ( select
> min(file#||'.'||dbablk) fb,
> sum(decode(state, 1, 1, 0)) cur,
> sum(decode(state, 3, 1, 0)) cr
> from
> sys.x_$bh
> where
> inst_id = userenv('Instance') and
> state in (1, 3)
> group by
> obj,
> class
> ) b,
> sys.apt_extents e
> where
> e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1) +1
> ---------- This line is comparing the file# wrongly, it should be
like
> this
>
> e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1) (without +1)
> substr(b.fb, instr(b.fb, '.') + 1) between e.block_id and e.block_id +
> e.blocks - 1
> group by
> e.owner||'.'||e.segment_name
> order by
> 2
> /
>
>
> I notice this mistake becouse I have another script that shows the buffers
> cached in memory and the results of both of them was so different. The
> problem is that when it gets the segment_name, it looks the block in other
> file.
>
> I hope you mail me to answer if I am wrong.
>
> Thank you.
>
> Silvina Botindari.
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Silvina Botindari
  INET: sbotindari_at_imrsa.com.ar

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).