Re: Sort_area_size ora-0068

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Mon, 1 Sep 2014 18:04:52 +0100
Message-ID: <DUB402-EAS110B40BF7C205771CF90DEA1C60_at_phx.gbl>



That's a problem copying to phone.
1835298655 vs
2147483647

Job interface is DBMS_JOB calling stored proc.

It can't be a shrink operation because that (2147483647) is the max on this platform.

The error itself is unexpected - if you exceed the max for your platform then you'd expect an ORA-02017: integer value required.

The order of min & max is also strange.

I'm not looking for a comprehensive review, changing code is possible, I don't like the code anyway.

But my question really just stems from observations that the same code on the same database running in the same job is sometimes able to set the value and sometimes not.

This naturally triggers the question "why?" and whether it's a good/safe idea for code to be setting this, particularly when it is an oft recommended approach for working with large sets of data.

It also raises the question of what else was running on the db at this time, how much memory was being used, and comes back to whether it is a sensible thing to do.

Sent from my iPhone

> On 1 Sep 2014, at 15:45, "Mark W. Farnham" <mwf_at_rsiz.com> wrote:
>
> 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-l
Received on Mon Sep 01 2014 - 19:04:52 CEST

Original text of this message