Showing results for 
Search instead for 
Did you mean: 

Oracle Input Buffer Limitation

David Cogley_1
Occasional Advisor

Oracle Input Buffer Limitation

A Perl script which uses DBI to copy information from production servers to a data warehouse functions without problem on Sybase but freezes on Oracle when the UPDATE statement gets to be too big.

For a particular day and server, I successfully copy over 200 records and the script freezes at the attached update statement. Upon inspection, it seems that this statement is just a little longer than the successful statements. When I paste this statement into SQL*Plus and add a semicolon, Oracle complains that a quoted string is not ended properly. If I just insert a carriage return in the middle of the statement, it executes successfully.

Is there a way to change the input buffer so it will successfully process a longer statement?

The data warehouse is implemented with Oracle 8.1.7 on WIN2K. The WIN2K box has only 128 MB RAM. This might be a contributing factor.
Andreas D. Skjervold
Honored Contributor

Re: Oracle Input Buffer Limitation


Have seen some references to multicolumn insterts spinning with more than 256 columns.

As I could see yours has 140, so you should be in the safeside here.

128 MB RAM is low but still your able to run the other updates... Beats me!

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Bill Hassell
Honored Contributor

Re: Oracle Input Buffer Limitation

Although this is an HP-UX (Unix) forum, experience with large scale databases such as Oracle indicate that memory should be measured in gigabytes for reasonable performance. Also, WinXX platforms have a long history of poor memory management, that is, when most of memory is used, swapping is not always effective and the lock-up scenario you describe is common to all programs when RAM is almost gone. I would increase RAM to a minimum of 512 megs but 1000 megs would be better.

Bill Hassell, sysadmin