Hi guys,
We have a long-running query (CTAS) that throws up ORA-01555. This is
the only query running against the database, which is Oracle9i
(9.2.0.6). Also, automatic undo is turned off on instructions from the
application vendor. Increasing the rollback tablespace makes this error
go away although the high water mark does not justify the increase in
tablespace size.

My question is: What are all the possible causes of this error and what
can be done in each case?

Thanks.

"kwr2k" <Kanwar.Plaha@gmail.com> wrote in message
news:1133734763.039658.176820@g14g2000cwa.googlegr oups.com...
> Hi guys,
> We have a long-running query (CTAS) that throws up ORA-01555. This is
> the only query running against the database, which is Oracle9i
> (9.2.0.6). Also, automatic undo is turned off on instructions from the
> application vendor. Increasing the rollback tablespace makes this error
> go away although the high water mark does not justify the increase in
> tablespace size.
>
> My question is: What are all the possible causes of this error and what
> can be done in each case?
>
> Thanks.
>

From 9.2 manual

ORA-01555 snapshot too old: rollback segment number string with name
"string" too small

Cause: Rollback records needed by a reader for consistent read are
overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of
UNDO_RETENTION. Otherwise, use larger rollback segments.

Theory: What if you created this table piecewize?
CTAS {w limit}
INSERT INTO {select from w limit}...
INSERT INTO {select from w limit}

Would be interesting to test...

--

Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

DO NOT REPLY TO THIS EMAIL
Reply only to the group.

kwr2k wrote:
> Hi guys,
> We have a long-running query (CTAS) that throws up ORA-01555. This is
> the only query running against the database, which is Oracle9i
> (9.2.0.6). Also, automatic undo is turned off on instructions from the
> application vendor. Increasing the rollback tablespace makes this error
> go away although the high water mark does not justify the increase in
> tablespace size.
>
> My question is: What are all the possible causes of this error and what
> can be done in each case?
>
> Thanks.

9i? Automatic undo is turned off on instructions from the vendor?

Get a new vendor.

But given you current situation ... enlarge your rollback segments ...
all of them ... and they should all be the same larger size.

Then read what Tom Kyte says about this.

And put some effort into finding a competent vendor.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

On 4 Dec 2005 14:19:23 -0800, "kwr2k" <Kanwar.Plaha@gmail.com> wrote:

>Hi guys,
>We have a long-running query (CTAS) that throws up ORA-01555. This is
>the only query running against the database, which is Oracle9i
>(9.2.0.6). Also, automatic undo is turned off on instructions from the
>application vendor. Increasing the rollback tablespace makes this error
>go away although the high water mark does not justify the increase in
>tablespace size.
>
>My question is: What are all the possible causes of this error and what
>can be done in each case?
>
>Thanks.


Question: do you know how Google works? Yes? PLEASE, USE IT, before
posting, This is a FAQ and the issue has been dealt with AD NAUSEAM!!!

I would also recommend searching a different vendor. Disabling
automatic undo is just utterly plain stupid.

--
Sybrand Bakker, Senior Oracle DBA

Okay, thanks for all the replies. I have googled for this issue (ad
nauseam, actually). However, there haven't yet seen a satifactory
explanation hence I decided to raise it at this forum to get specific
answers.

Anyway, two things stand out clearly from all your suggestions:

1. Increase rollback segment size -- Yes, I am going to try this next
as soon as the application team is ready
2. Get a new vendor -- Yes, I would love to do this but is an
impossibility given the size and stage of the project we are in

Cheers.

"kwr2k" <Kanwar.Plaha@gmail.com> wrote in
news:1133742197.959227.119420@z14g2000cwz.googlegr oups.com:

> Okay, thanks for all the replies. I have googled for this issue (ad
> nauseam, actually). However, there haven't yet seen a satifactory
> explanation hence I decided to raise it at this forum to get specific
> answers.
>
> Anyway, two things stand out clearly from all your suggestions:
>
> 1. Increase rollback segment size -- Yes, I am going to try this next
> as soon as the application team is ready
> 2. Get a new vendor -- Yes, I would love to do this but is an
> impossibility given the size and stage of the project we are in
>

One common cause of ORA-01555 is when a COMMIT exists within a long running
cursor that is changing (INSERT/UPDATE/DELETE) data.

Hi IANAL_VISTA,
In this case, there is no commit till the end of the transaction.

UPDATE: I have set the optimal size to NULL for now. Waiting for
results from the application team. Will post them here (and probably
more questions).

Thanks all.

"kwr2k" <Kanwar.Plaha@gmail.com> wrote in message
news:1133734763.039658.176820@g14g2000cwa.googlegr oups.com...
> Hi guys,
> We have a long-running query (CTAS) that throws up ORA-01555. This is
> the only query running against the database, which is Oracle9i
> (9.2.0.6). Also, automatic undo is turned off on instructions from the
> application vendor. Increasing the rollback tablespace makes this error
> go away although the high water mark does not justify the increase in
> tablespace size.
>
> My question is: What are all the possible causes of this error and what
> can be done in each case?
>
> Thanks.
>


1) Other DML activity going on against the
data you are reading for the CTAS.

2) Your own session causing delayed block
cleanout on the data you are reading.

In either case, the first step is to check on the
efficiency of the query that the CTAS is running -
the quicker it runs, the less chance there is for
other people to generate excess rollback; the
fewer (redundant) data blocks it visits the less
likely it is to cripple itself with delayed block
cleanout.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005

If you can't tune your sql to be more efficient, you can try to
separate the long query into some small parts.

Sybrand Bakker wrote:
> On 4 Dec 2005 14:19:23 -0800, "kwr2k" <Kanwar.Plaha@gmail.com> wrote:
>
> >Hi guys,
> >We have a long-running query (CTAS) that throws up ORA-01555. This is
> >the only query running against the database, which is Oracle9i
> >(9.2.0.6). Also, automatic undo is turned off on instructions from the
> >application vendor. Increasing the rollback tablespace makes this error
> >go away although the high water mark does not justify the increase in
> >tablespace size.
> >
> >My question is: What are all the possible causes of this error and what
> >can be done in each case?
> >
> >Thanks.
>
>
> Question: do you know how Google works? Yes? PLEASE, USE IT, before
> posting, This is a FAQ and the issue has been dealt with AD NAUSEAM!!!
>
> I would also recommend searching a different vendor. Disabling
> automatic undo is just utterly plain stupid.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Don't know why I bother, but Mr. Bakker, if you're just going to slam
someone, it's best not responding at all.