Re: anyway to flush the db_buffer_cache?

I'am sorry if i was not clear in the last reply. when a tablespace is taken offline, the blocks belonging to all the files in that tablespace are marked free and effectively useless. Example-
I have a table FS in the tablespace FSCAPACITY.

I open an SQL session an issue the following statements

sys_at_DBA> set autot on stat
sys_at_DBA> select count(*) from fscap.sg;

  COUNT(*)

     58720

1 row selected.

Statistics

          0  recursive calls
          4  db block gets
        505  consistent gets
        506  physical reads
          0  redo size
        201  bytes sent via SQL*Net to client
        316  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys_at_DBA> select count(*) from fscap.sg;

  COUNT(*)

     58720

1 row selected.

Statistics

          0  recursive calls
          4  db block gets
        505  consistent gets
          0  physical reads
          0  redo size
        201  bytes sent via SQL*Net to client
        316  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sys_at_DBA> alter tablespace fscapacity offline;

Tablespace altered.

sys_at_DBA> alter tablespace fscapacity online;

Tablespace altered.

sys_at_DBA> select count(*) from fscap.sg;

  COUNT(*)

     58720

1 row selected.

Statistics

          0  recursive calls
          4  db block gets
        505  consistent gets
        506  physical reads
          0  redo size
        201  bytes sent via SQL*Net to client
        316  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, taking the FSCAPACITY tablespace effectively flushed all the blocks. So the last statement again recorded 506 physical reads.

regards
Srivenu Received on Wed May 07 2003 - 06:21:44 CDT