Re: rollback segment too small

Essentially , you are always likely to encounter this when running *long* queries online against a volatile database.

Any DML happening to the ENTRIES table prior to your query will take a snapshot of the rows that are about to be modified into a rollback segment. Your query then comes along and has to use this snapshot, as it is the best Oracle can provide at that time (the *actual* rows may be changing). The DML transaction finishes (say) while your query is still running. Another DML transaction comes along and is allocated the *same* rollback space that your query is using ...

The transaction is deemed by Oracle to have priority over your query, so the snapshot your query is looking at gets overwritten. Your query then fails with 'snapshot too old/rollback segment too small'.

The moral of the story is that you can only rely on 'long' queries during 'quiet' periods such as overnight, preferably in a single-job queue environment where you know that your query is not in conflict with any DML that might be going on.

Hope this helps :>

Glenn Baron

Bob hammond <bhammond_at_mirror-image.com> wrote in article <XdKT2.94$7j4.21017_at_news.shore.net>...
> Hi, everybody!
> I have 6 questions about very big and very active database.
> I hope other people already met (and solved) the problems
> I am fighting with. I will appreciate any definite answers,
> hints, comments, proposals, etc.
> Thank you in advance.
> Andrey Radul
> aradul_at_mirror-image.com
>
> We have ORACLE database under UNIX on Sun Solaris.
> I work with table ENTRIES that has about 13 million records.
> >From 2 to 5 million records change every day.
> I used OCI to execute the SQL statement
>
> select count(*) from ENTRIES
>
> and it gave me ORACLE error message ORA-1555 "rollback segment too
small".
>
> 1.What are possible ways to make sure that simple SQL statements will not
> crash?
>
> Currently I extract (approximate) number of rows in ENTRIES table using
> SQL commands
>
> analyze table ENTRIES estimate statistics sample 1 percent
>
> and
>
> select num_rows from TABS where table_name = 'ENTRIES'
>
> It works for the moment.
>
> 2.How I could make sure that it is not going to crash later?
> (our database tend to grow very fast)
>
> 3.I am working on a program that should adjust it's behavior depending on
> whether our tables are close to be full. The only way to get this
> information
> I have found so far is to use "analyze table ..." statement
> (dbms_space.unused_space procedure is based on the same "analyze table
.."
> statements). Am I correct? Are there other (more reliable) methods to
> get (estimate) the same information?
>
> 4. Suppose, there are no other ways to get (estimate) the percentage of
how
> full our database is. What precautions should I take to make sure that
> my "analyze table ..." statements are not going to crash in the future?
> For instance, suppose I will use a dedicated rollback segment for
> "analyze table ..." statements.
> Should I increase the size of that rollback segment proportionally to
size
> of
> the whole database?
> 5. Another idea is to recreate the ENTRIES table as partitioned table.
> Will partitioning help to solve the reliability problem?
> 6. I can catch ORACLE error if "analyze table ..." crashes.
> Are there any absolutely reliable ways (which do not crash regardless of
the
> table
> size ) to do emergency cleanup of our table(s)? In the case of emergency
> I would like to delete, say, 10% of all records.
>
>
>
>
Received on Sat Apr 24 1999 - 09:45:27 CDT