OraFAQ Forum: SQL & PL/SQL » ORA-20000: ORU-10027: buffer overflow

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-20000: ORU-10027: buffer overflow (oralce 10gR2 on windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-20000: ORU-10027: buffer overflow [message #478280]

Thu, 07 October 2010 12:12

enjay
Messages: 15
Registered: September 2010

Junior Member

Hello Every one,

I am running script which drop all public synonyms and create private synonyms for all eligible objects. I am passing value to procedure through cursor and I am using dbms_output.

 
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 999999 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 101

I read that by: SET SERVEROUTPUT ON SIZE unlimited
would resolve this problem.

What are the effects?
Do I need to check any disk space, or any effect on the memory?

Thanks,
enjay

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow [message #478281 is a reply to message #478280]

Thu, 07 October 2010 12:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What are the effects?
no more error

>Do I need to check any disk space, or any effect on the memory?
only if you are paranoid

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow [message #478282 is a reply to message #478280]

Thu, 07 October 2010 12:18

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

I believe that 1,000,000 (1 million) is the max. Based on your error message, it sure looks that way.

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow [message #478285 is a reply to message #478282]

Thu, 07 October 2010 12:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> set serveroutput on size unlimited
SQL>

works for me

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow [message #478288 is a reply to message #478285]

Thu, 07 October 2010 12:31

enjay
Messages: 15
Registered: September 2010

Junior Member

Thank you BlackSwan for your input.
So I can set the serveroutput on size unlimited and the run the script with out any effects even if there are millions input.

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow [message #478289 is a reply to message #478288]

Thu, 07 October 2010 12:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>So I can set the serveroutput on size unlimited and the run the script with out any effects even if there are millions input.
It will succeed when enough resources are available & fail when resource limitation is exceeded.

Report message to a moderator

Re: ORA-20000: ORU-10027: buffer overflow [message #478353 is a reply to message #478282]

Fri, 08 October 2010 03:11

flyboy
Messages: 1903
Registered: November 2006

Senior Member

joy_division wrote on Thu, 07 October 2010 19:18

I believe that 1,000,000 (1 million) is the max. Based on your error message, it sure looks that way.

Why not check it? As enjay posted his Oracle version, there is nothing preventing us to consult SQL*Plus User's Guide and Reference book. This chapter describes changes in 10gR2: http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/whatsnew.htm#i969790
Quote:

SET SERVEROUTPUT supports an UNLIMITED size which is the new default.

Following part contains only guessing: serveroutput size was set in some script to 999999 to increase it as its default value was low in older Oracle version - see the description for 9iR2 here: http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012919 Quote:

SIZE sets the number of bytes of the output that can be buffered within the Oracle8i or Oracle9i database server. The default for n is 2000. n cannot be less than 2000 or greater than 1,000,000

Report message to a moderator

Previous Topic:	html parse
Next Topic:	column to row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 26 05:48:52 CDT 2025