Re: Rollback Segments

In article <2d0ibu$nai_at_snoopy.sra.com>, cohen_at_pni.sra.com (Janine Cohen) writes:
> hello, I am new to using ORACLE V7.0.15 and I have a question about rollback
> segments. I know that each transaction can only write to one rollback
> segment. I have a transaction that delete about 200 megs of information.
> I was wondering if there is any way to get around having a 200 meg rollback
> segment allocated (we don't want to waste so much space if we can help it).
>
> Specifically - is it possible to commit portions of a large delete so that
> that part of the rollback segment info could become inactive and thus reuse
> the existing extents?

Unfortunately, Oracle does NOT allow you to specify an automatic or transparent commit on their base sql*plus commands. You will need to code a PL/SQL block which opens the cursor, processes 1000 deletes, closes the cursor, commits, opens the cursor, processes 1000 more, etc etc etc.

If you are cleaning out all records from a table then have a look at the TRUNCATE command which doesn't use the rollback segments.

If you are deleting more than you are keeping then have a look at creating another table like the original and use the COPY command to move the 'undeleted' records to the new table and drop the old table and rename the new table etc. This is useful because COPY allows you to specify a commit point using the COPYCOMMIT and ARRAYSIZE variables. Can be used as a substitute for the "INSERT into X as select Y from Z" construct when processing mucho records.

>
> I would appreciate any information that you could give me.
>
> Thanks, Jeanine Cohen
> SRA Corporation
> Fairfax, Virginia
>

hope the above is of some use.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of                                      * 
*          Health, Housing, Local Government & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************