Re: Help with rollback problems

If possible can you use the following statement to create the second table rather than inserting the data into an already existing table:

CREATE TABLE second_table UNRECOVERABLE AS SELECT * FROM first_table WHERE blah-de-blah-de-blah;

This is the fastest way to move data around within the database and avoids using rollback segment. If you want to perform this from within a stored procedure than you can do so using the DBMS_SQL package to implement the above statement using dynamic sql.

The "snapshot too old" problem is occurring because you have two process using the same rollback segment. One is a long running query that is reading data blocks from the rollback segment and the other is a transaction that is overwriting one or more of the data blocks used by the query. A quick fix is to increase the size of the rollback segment in the hope that the transaction modifying the data blocks will not need to use any of the blocks you are reading for the query. This is not guaranteed to work. The only thing that you can be sure will work is if you can ensure that no modifications are being done by other processes when you are performing this query.

If you are still not getting anywhere with this, mail me directly and i will help you sort this out.

Graeme Hinchliffe wrote:

> Hiya
> I have recently started working with Oracle 8.0.5. The task I
> am trying to achieve is to copy a large number of rows from one table
> to another to speed up a query. The number of rows is around the
> 200,000 figure from a table of about 1 million. The problem I am
> getting is that when I run my query (which annoyingly worked first
> time) I get this error:
>
> ORA-01555: snapshot too old: rollback segment number 9 with name "RG3"
> too small
>
> I have tried to adjust the rollback setings with the Oracle Storage
> manager, but whenever I set a new rollback segment to Online I then
> cannot access it any longer and get the error..
>
> ERROR initializing rollback ORA-00942 table or view does not exist.
> If I right click on a rollback segment it crashes the client!
>
> I have tried increasing the size of the RBS table to 100 Meg but still
> get this error (it was initially set to about 10K!! (would I need to
> add a new rollback segment for it to use this new space that I have
> added?
>
> Also is there a way of turning off the rollback, so data is writen
> directly to the db without going through the rollback ??
>
> Please help.. this is driving me nuts..
>
> Thanx in advance
>
> Graeme
Received on Tue Jul 06 1999 - 23:32:21 CEST