Re: ORA-01555 error

"kwr2k" <Kanwar.Plaha_at_gmail.com> wrote in message news:1133734763.039658.176820_at_g14g2000cwa.googlegroups.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.