Rollback Segment Errors

[tkestell]

Is their anyway to perform mass deletes (several million records) without
"maxing out" rollback segments?

I'm working on archiving data from an Oracle 8.1.7 database The system is
about 4 years old and no data has EVER been archived /removed. So the
two largest tables contain well over 25 million records!

As part of the archive process I'm deleting out-dated records, but because
of the sheer volume keep receiving "Unable to extend Rollback Segment
errors." I've tried to commit every 50 deletes, I've even tried committing
after
every single delete, but eventually always receive this error. Our DBA
does not want to extend the rollback segments (probably with good reason).

This process will run off-hours, so there's no conflict with end-users.

TIA for any insights you can provide.
Tomas

[varol cavdar]

Hi Tomas
The only way to delete table records without using a rollback
segment is to use truncate command. In this case all records in your
table will be deleted and committed implicitly.

Another method is to select a bigger rollback segment. To do this
you should use "SET TRANSACTION USE ROLLBACK SEGMENT rbig;" command at
the begining of your transaction. If rbig is big enough then you can
delete much more records at a time

best regards..

[tkestell]

Varol,
Thanks very much...I'll talk to DBA about using a bigger segment with
SET TRANS...

Thanks again
Tomas
"varol cavdar" <va***@bilisim-ltd.com.tr> wrote in message
news:e0**************************@posting.google.c om...

Hi Tomas
The only way to delete table records without using a rollback
segment is to use truncate command. In this case all records in your
table will be deleted and committed implicitly.

Another method is to select a bigger rollback segment. To do this
you should use "SET TRANSACTION USE ROLLBACK SEGMENT rbig;" command at
the begining of your transaction. If rbig is big enough then you can
delete much more records at a time

best regards..

[Daniel Roy]

The most efficient way to perform "mass deletes" is to truncate. If
you have to delete all the records, it's very efficient and
straightforward. If you need to keep only a portion of the existing
records in a table, the way I would try to handle it is by
disabling/dropping the indexes, copying the records to be kept in a
temporary table (and I DON'T mean temporary in the Oracle sense, but
in the conceptual sense), truncate or drop the initial table, and
finally recopy the records from the "temporary" to the initial table.
Use "create table ... as select ... where ..." to move records around.
Truncate the initial table if there is something worth keeping
(grants, triggers, procedures referencing it, extents allocated to it,
constraints, ...), otherwise just drop it. Don't forget to rebuild the
indexes and constraints (if applicable) afterwards.

Daniel

Is their anyway to perform mass deletes (several million records) without
"maxing out" rollback segments?

I'm working on archiving data from an Oracle 8.1.7 database The system is
about 4 years old and no data has EVER been archived /removed. So the
two largest tables contain well over 25 million records!

As part of the archive process I'm deleting out-dated records, but because
of the sheer volume keep receiving "Unable to extend Rollback Segment
errors." I've tried to commit every 50 deletes, I've even tried committing
after
every single delete, but eventually always receive this error. Our DBA
does not want to extend the rollback segments (probably with good reason).

This process will run off-hours, so there's no conflict with end-users.

TIA for any insights you can provide.
Tomas

[Mark D Powell]

"tkestell" <tk******@attbi.com> wrote in message news:<lSPKa.28822$3d.17163@sccrnsc02>...

Is their anyway to perform mass deletes (several million records) without
"maxing out" rollback segments?

I'm working on archiving data from an Oracle 8.1.7 database The system is
about 4 years old and no data has EVER been archived /removed. So the
two largest tables contain well over 25 million records!

As part of the archive process I'm deleting out-dated records, but because
of the sheer volume keep receiving "Unable to extend Rollback Segment
errors." I've tried to commit every 50 deletes, I've even tried committing
after
every single delete, but eventually always receive this error. Our DBA
does not want to extend the rollback segments (probably with good reason).

This process will run off-hours, so there's no conflict with end-users.

TIA for any insights you can provide.
Tomas

Tomas, if you are commiting as you go you should not be getting an
'unable to extend' error unless your rbs tablespace is badly
fragmented and some segments have no room to grow at all, or the
maximum size of your rbs segmens is less than the amount of data you
are trying to purge.

May I suggest you first verify that your rbs tablespace/segments do
not require manual intervention to clean them up. I would suggest
running multiple purges with each purge going after the oldest data
such as data < 3.75 yrs, then 3.5, then 3.25 or similar where the
amount of data in each pass will fit in the maximum size of a rollback
segment.

You may want to allocate a special large rbs segment just to support
this process. The following article may be of interest:

How do I associate an active session with a rollback segment ?
http://www.jlcomp.demon.co.uk/faq/active_rollback.html

HTH -- Mark D Powell --

[tkestell]

Daniel,
The more people I talk with the more it sounds like the majority of
DBA types consider your solution the better and most efficient one.

Thanks for your insight ... I'll pursue your suggestions.
Tomas
"Daniel Roy" <da*************@hotmail.com> wrote in message
news:37************************@posting.google.com ...

The most efficient way to perform "mass deletes" is to truncate. If
you have to delete all the records, it's very efficient and
straightforward. If you need to keep only a portion of the existing
records in a table, the way I would try to handle it is by
disabling/dropping the indexes, copying the records to be kept in a
temporary table (and I DON'T mean temporary in the Oracle sense, but
in the conceptual sense), truncate or drop the initial table, and
finally recopy the records from the "temporary" to the initial table.
Use "create table ... as select ... where ..." to move records around.
Truncate the initial table if there is something worth keeping
(grants, triggers, procedures referencing it, extents allocated to it,
constraints, ...), otherwise just drop it. Don't forget to rebuild the
indexes and constraints (if applicable) afterwards.

Daniel

Is their anyway to perform mass deletes (several million records) without "maxing out" rollback segments?

I'm working on archiving data from an Oracle 8.1.7 database The system is about 4 years old and no data has EVER been archived /removed. So the
two largest tables contain well over 25 million records!

As part of the archive process I'm deleting out-dated records, but because of the sheer volume keep receiving "Unable to extend Rollback Segment
errors." I've tried to commit every 50 deletes, I've even tried committing after
every single delete, but eventually always receive this error. Our DBA
does not want to extend the rollback segments (probably with good reason).
This process will run off-hours, so there's no conflict with end-users.

TIA for any insights you can provide.
Tomas