RE: Sort_area_size ora-0068
Date: Mon, 1 Sep 2014 10:43:24 -0400
Message-ID: <030e01cfc5f3$166b4f90$4341eeb0$_at_rsiz.com>
I'm not sure, but it is suspicious because (obvious if you add the thousands delimiters)
1,835,298,655 is greater than 214,783,647
So it is AT LEAST an erroneous error message bug.
I'm not sure where the 1.8 billion limit comes from in your stack. Do you know? What error tosses when you exceed that by 1 manually from sql*plus? (Do you even get an error?)
What is the job interface (since you have implied it is *not* sql*plus) ?
The root cause *might* be some type conversion error.
The root cause *might* be something about already made during the running process or transaction, so a change at that point in your process cannot be made. Perhaps it cannot shrink if a given job has already used more.
As I wrote, "I'm not sure."
An early work-around attempt would be to make this be the very first thing the job does. Still, a full explanation is in order unless what is different between executions that generate the error versus executions that succeed can be made. "Erratic" just doesn't cut it.
I hope this helps,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Dominic Brooks
Sent: Monday, September 01, 2014 8:58 AM
To: oracle-l_at_freelists.org
Subject: Sort_area_size ora-0068
Why might a process erratically report an ORA-00068 for a consistent "alter session set sort_area_size"?
There is some code setting workarea_size_policy to manual and attempting to set sort_area_size to 2147483647.
The value is hardcoded so it doesn't change. But sometimes the code works, sometimes it doesn't. Code was called from a job on a two node cluster.
Error when raised is consistent:
ORA-00068: invalid value 214783647 for parameter sort_area_size, must be
between 1835298655 and 0.
Running the commands manually in a sqlplus session works consistently.
Thoughts?
Cheers,
Dominic
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 01 2014 - 16:43:24 CEST