Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL job failing - extents?

Re: PL/SQL job failing - extents?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 17 Oct 2006 16:55:28 -0700
Message-ID: <1161129328.382192.112530@e3g2000cwe.googlegroups.com>

On Oct 17, 6:41 pm, "Andrew" <andrewgoth..._at_Nospamthanks.com> wrote:
> First up - sorry if this is a daft question - got a fair bit of SQL Server
> and Informix background - Oracle - total newbie.
> Got a PL/SQL run which fails fairly regularly with a fetch out of sequence
> error. Usually I know this is because you try to fetch after closing a
> cursor - particularly during nested fetches.
> However - this problem - without changing the data at all (transfer and
> additional information from one table - plus lookups to another) runs OK
> most of the time when re-run. So - my admittedly rough guess is some kind
> of contention. Now - in some cases in SQL Server - Informix, this can be
> caused by the table having a new extent applied part way through the job -
> and again the job will go ok on re-run as there's now enough space for the
> job. I asked our Oracle bods whether this may be the case, and when the
> last extent was allocated to the target table - to see if it co-incided with
> the job failure. Got back a snotty "I've been working with Orcle for 10
> years" reply "and there's no easy way to do this"
> The two questions are - is the additional extent issue likely, secondly -
> how can you check the date and time of the last extent allocation for a
> given table?
>
> cheers
> Andrew

You can dump the online redo log looking for operation code 14.4, but extent allocation should not be your code's problem. Note that not all 14.4's are extent allocations.

I would suggest checking to make sure NULL values do not cause the wrong code path to be followed and to look at the error handers and what happens to the logic flow when errors occur. Error handlers often need to close open cursors before the logic continues.

HTH -- Mark D Powell -- Received on Tue Oct 17 2006 - 18:55:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US