Sunday, May 30, 2010

How to Clear Buffer Cash and Shared Pool in Oracle 10g

Flush buffer cache to clear recently cached data blocks
SQL> alter system flush buffer_cache;
System altered.

Flush shared pool to clear recently created SQL parse and execution plan
SQL> alter system flush shared_pool;
System altered.

What is the Difference between “clear buffer” and “alter system flush buffer_cache

Clear Buffer SQL* Plus Command which is use to clear the SQL*Plus screen and the screen buffer.
Syntax:
CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING}

SQL> clear buffer
buffer cleared

Example:
SQL> Select empno, ename from Scott.emp Where ename='KING';
     EMPNO ENAME
---------- ----------
      7839 KING
SQL> list;
  1* Select empno, ename from Scott.emp Where ename='KING'
SQL> clear buffer;
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.

But alter system flush buffer_cache use to clear data buffer cash in SGA.

No comments:

Post a Comment