Re: Rollback error situation

From: Brad Whitlock <brad.whitlock_at_honeywell.NOSPAM.com>
Date: Mon, 16 Oct 2000 13:02:52 -0500
Message-ID: <8sffpl02vf0@enews4.newsguy.com>

How many seqments are online? Are you specifying a particular segment for the app to use, or letting Oracle pick one?

Segment shrinking only happens when a transaction is assigned to a segment, not when one finishes. So if you have say 3 segments and aren't specifying one, the situation would be:

Tablespace = 1000MB, Seg1 = 800MB (ready to be shrunk), Seg2 = 50MB, Seg3 = 50MB.

Job-A needs 400MB.
Job-A was assigned to Seg2, and crashed.
Job-A was restarted, assigned to Seg3, and crashed.

Since no other jobs were running, nothing was assigned to Seg1, so it never automatically shrank.
When you manually shrunk the segments (including Seg1), the space was freed up and Job-A had what it needed.

As to why now, after a year of no problems: Did someone run a special job, possibly from SQL*Plus, that consumed more segment space than ever before? Was the rollback configuration changed?

Brad Whitlock Honeywell International

<fsiegel_at_my-deja.com> wrote in message news:8sf72h$ie5$1_at_nnrp1.deja.com...
> Job-A crashed because it cannot extend the rollback tablespace.
> Job-A was restarted and came down again with same reason.
>
> The applications people are absolutely certain that no other jobs were
> running and that the data amounts did not increase. This procedure has
> been running without fault for 1 year. When I did a manual shrink of
> the rollback segment, restarted the job, the job worked fine - no
> rollback problems!!
>
> Shrinks to Optimal are not happening automatically according to
> v$rollstat (# of shrinks remains constant before and after the job
> runs). There are many wraps and extends. Can anyone estimate what's
> going on here?
>
> Much Thanks,
> Frank S.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Oct 16 2000 - 13:02:52 CDT