Re: Rollback segments

On 3 Sep 98 15:50:23 GMT, "Kevin Gray" <kevin.gray_at_andata.co.uk> wrote:

>
>Hi there
>
>I am writing some SQL scripts with Unix shell as well to do some very large
>updates (could be moving 3.5million records in insert/select combination).
>
>Can anyone provide answers to the following.
>
>1. Is it possible to have a single insert/select run without requiring a
>rollback segment to use?

You can use SET TRANSACTION READ ONLY immediately preceeding a SELECT, and minimal rollback will be used. You cannot do massive updates without using a rollback segment at all. What would the database do if the process failed for some reason? You can however, process data in pieces ... doing a COMMIT every 10000 records or so. This allows you to use a smaller rollback segment than if you try to process all the 3.5 million records before ever issuing a COMMIT.

Another trick is to set up only one large rollback and use the SET TRANSACTION USE ROLLBACK SEGMENT routine to force Oracle to select the large rollback for the transaction in mind. Otherwise, the db engine will randomly select an available rollback segment during the transaction and could potentially select a small one that would result in an ORA error.

sooz

>2. Is it possible to use multiple rollback segments for a single
>insert/select transaction?
>3. Is it possible to use multiple files in a single rollback segment so
>that the rollback segment can be split over several files and, possibly,
>disks?
>
>Also, does anyone know exactly the best way to work out how much space to
>allow for rollback segments.
>
>Any help would be very much appreciated.
>
>Thanks.
>
>Regards
>
>Kevin Gray
>
>Kevin Gray
>Senior Consultant
>The Customer Engagement Company, UK
>Email: kevin.gray_at_h2engage.co.uk
>WWW: http://www.h2engage.co.uk/
Received on Thu Sep 03 1998 - 23:27:01 CDT