Flushing DBMS Buffer

alter system flush buffer_cache;
it works in 10g

but can you give the exact error code?

is it the buffer limit of dbms_output.put_line

if so use
dbms_output.enable(32767); once in the session before executing the put_line command

Ah sorry Im on 8i

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

then you will have to restart the database. Let me look up on the given error message. ORA 20000 is a user defined error. So can you tell me what tool you are using? or if possible can you post the exception area of ur pl/sql code.

did you try       

dbms_output.enable(32767);

You think I should restart the database?! Why?!!!!!!
Obviously if the buffer limit is 1000000 then dbms_output.enable must be greater than 32767 agreed?
In fact its implicitly called by setting SET serveroutput ON SIZE 1000000

oh. you are using
SET SERVEROUTPUT ON SIZE 1000000  already

this is the max which can be set. You will have to reduce the amount of data you are printing out using dbms_output.put_line.

Or alternatively flush the buffer...... Next taker please!

I think our messages crossed each other.

anyhow
 
check
there is no problm with Ur procedure, but problm with the SQL editor output buffer setting,
if the output (DBMS_OUTPUT.PUT_LINE) is exceeding the default limit(2000), one will get that err.

It is possible to set the output limit as follows-
----------
SQL> exec dbms_output.enable(20000);

PL/SQL procedure successfully completed.
-------------
Max value for this setting is - 1,000,000

for more details - look at
http://download-east.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89852/dbms_ou2.htm

ask the site admins for a PAQ/refund. They will give it to you as your answer was not given.

But if you can try out the anser asktom has given
use a plsql table type and a select -- write your own dbms_output (that has
no 1,000,000 byte limit and limits the linesize to 2000 bytes/4000 bytes in
Oracle7/8, not 255).  I tested this in 7.3.4 and it works well.  it would look
like this:
tkyte@ORA734.WORLD> create or replace package my_dbms_output
  2  as
  3      procedure put( s in varchar2 );
  4      procedure put_line( s in varchar2 );
  5      procedure new_line;
  6  
  6      function get_line( n in number ) return varchar2;
  7      pragma restrict_references( get_line, wnds, rnds );
  8  
  8      function get_line_count return number;
  9      pragma restrict_references( get_line_count, wnds, rnds, wnps );
 10  
 10      pragma restrict_references( my_dbms_output, wnds, rnds, wnps, rnps );
 11  end;
 12  /

Package created.

thats our interface, much like the existing dbms_output (but no disable/enable
-- this is always enabled).  We implement the package body as such:

tkyte@ORA734.WORLD> create or replace package body my_dbms_output
  2  as
  3  
  3  type Array is table of varchar2(4000) index by binary_integer;
  4  g_data        array;
  5  g_cnt        number default 1;
  6  
  6      procedure put( s in varchar2 )
  7      is
  8      begin
  9          if ( g_data.last is not null ) then
 10              g_data(g_data.last) := g_data(g_data.last) || s;
 11          else
 12              g_data(1) := s;
 13          end if;
 14      end;
 15  
 15      procedure put_line( s in varchar2 )
 16      is
 17      begin
 18          put( s );
 19          g_data(g_data.last+1) := null;
 20      end;
 21  
 21      procedure new_line
 22      is
 23      begin
 24          put( null );
 25          g_data(g_data.last+1) := null;
 26      end;
 27  
 27      function get_line( n in number ) return varchar2
 28      is
 29          l_str varchar2(4000) default g_data(n);
 30      begin
 31          g_data.delete(n);
 32          return l_str;
 33      end;
 34  
 34      function get_line_count return number
 35      is
 36      begin
 37          return g_data.count+1;
 38      end;
 39  
 39  end;
 40  /

Package body created.

further details are given in
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:146412348066

Boy oh boy - all I want to know is how to flush the buffer!!!
Thanks for your efforts anand but can you stop now and let someone else have a go?

Please!!!! Flushing the buffer will not help with your problem. 1000000  is a limit which is set for dbms_output.put_line.
Please observe my statement
"use a plsql table type and a select -- write your own dbms_output (that has
no 1,000,000 byte limit and limits the linesize to 2000 bytes/4000 bytes in
Oracle7/8, not 255)."

I gave you my informed solution earlier. and told you to ask for a refund of your 500 points as dbms_output.put_line has no other solution. then I went a step ahead and still got a work around for you. Yes let others have a go. Thank you.

https://www.experts-exchange.com/questions/20148833/buffer-overflow-error.html#6273381

https://www.experts-exchange.com/questions/21773621/Setting-option-in-an-Oracle-stored-procedure.html#16187854

I've found that if I set DBMS_OUTPUT.DISABLE then DBMS_OUTPUT.ENABLE that seems to do it, but its a bit messy! Anyone got a better way?

messy? I can't even see it working. disable followed by enable therotically cannot work. let me check up on my database. any comments anyone else?