How to free buffer cache?

Shows how to flush buffer cache and v$bh, you may only want to do this on Dev or Test enviornment as it would affect performance on production.

-- displays the status and number of pings for every buffer in the SGA
SQL> select distinct status from v$bh;

STATUS
——-
xcur
cr

-- flush buffer cache for 10g and upwards
SQL> alter system flush buffer_cache;

System altered.

-- flush buffer cache for 9i and upwards
SQL> alter session set events ‘immediate trace name flush_cache’;

Session altered.

-- Shows buffer cache was freed after flushing buffer cache
SQL> select distinct status from v$bh;

STATUS
——-
free

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s