Link to home
Start Free TrialLog in
Avatar of stummj
stummjFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Flushing DBMS Buffer

I keep hitting the DBMS Buffer limit with some code I have written even when it is set to its maximum value.

What command do I need to flush the cache in PLSQL?
Avatar of anand_2000v
Flag of India image

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
Avatar of stummj


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       

Avatar of stummj


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

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.
Avatar of stummj


Or alternatively flush the buffer...... Next taker please!
I think our messages crossed each other.

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

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      function get_line( n in number ) return varchar2;
  7      pragma restrict_references( get_line, wnds, rnds );
  8      function get_line_count return number;
  9      pragma restrict_references( get_line_count, wnds, rnds, wnps );
 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  type Array is table of varchar2(4000) index by binary_integer;
  4  g_data        array;
  5  g_cnt        number default 1;
  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      procedure put_line( s in varchar2 )
 16      is
 17      begin
 18          put( s );
 19          g_data(g_data.last+1) := null;
 20      end;
 21      procedure new_line
 22      is
 23      begin
 24          put( null );
 25          g_data(g_data.last+1) := null;
 26      end;
 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      function get_line_count return number
 35      is
 36      begin
 37          return g_data.count+1;
 38      end;
 39  end;
 40  /

Package body created.
Avatar of stummj


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.
Avatar of techji

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stummj


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?