Re: "ORA-01555 snapshot too old and ORA-01658 unable to create

Newsgroups: comp.databases.oracle.misc
Subject: Re: "ORA-01555 snapshot too old and ORA-01658 unable to create References: <32ade686.2955303_at_News.scruznet.com>

rhaven_at_santacruz.com (Richard C Haven) writes:
>We are getting these errors:
>01555: snapshot is too old; rollback segment number 4 with name "R03"
>too small
>01658: unable to create INITIAL extant for segment
>during SQL operations.
>Any ideas what causes this and workarounds?
>TIA
Richard,

For the ORA-1555, it appears that your rollback segment "R03" is too small. You will need to use larger rollback segments. Drop and recreate with a larger INITIAL and NEXT extents. Also, unless you specifically used "R03" for the failed transaction, there is a good chance that your other rollback segments are too small. Consider increasing the size of all rollback segments.

For the ORA-1658, you could not create the object (table, index, etc.) because it was too large for the amount of free "contiguous" space in the tablespace. You can do one of two things: 1) Try recreating the object, but specify a smaller INITIAL size. This may work for the short term, but it could leave you with a tablespace almost completely filled. If the object grew in size, you run the risk of not being able to get a new extent.
2) Add a datafile to your tablespace with sufficient room to fit your object in.

Based on the two above errors, I would recommend reviewing all object sizes and tablespace sizes. You may have made them too small.

Best of luck.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->