Re: alter session set events = 'immediate trace name flush_cache'

kuroko_at_gmail.com wrote:
> You can try the following query.
> It gives you the count of blocks in buffer cache for each object.
>
> select o.owner,o.object_name,o.object_type,count(b.BLOCK#) "Blocks in
> Cache" from v$bh b, dba_objects o
> where b.OBJD=o.data_object_id
> group by o.owner,o.object_name,o.object_type
> order by 4 desc
>

The "flush" operation will retain blocks in the buffer cache but will make them free, after they're written to the disk. So called "flush" operation doesn't create an empty cache, so called "tabula rasa", it simply writes modified ("dirty") blocks to the disk and marks them as "free" blocks. Consequently, you will need also to query status field from V$BH and see how many blocks are free. I will not ask you why are you flushing the cache. If you're doing it, you know that it's the last resort, something not be done, unless there is no other choice or if you are in the sadistic mood.

-- 
Mladen Gogala
http://www.mgogala.com