Avatar of stummj
Flag 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?
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

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       


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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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.


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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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?