473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Rollback Segment Errors

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

Jul 19 '05 #1
5 12829
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..
Jul 19 '05 #2
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..

Jul 19 '05 #3
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

Jul 19 '05 #4
"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 --
Jul 19 '05 #5
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

Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alice | last post by:
Hi all, Given a rollback segment with storage as follows : Initial Extent 5 MB Next Extent 5 MB Min Extents 20 Does this mean that every time a transaction uses this rollback segment it...
2
by: Alice | last post by:
Hi all, I have a problem with rollback segment. I am a beginner and do not have much knowledge of DBA. Anyways, the problem is there is a transaction of around 105 MB. This would not fit...
2
by: Gert Schumann | last post by:
I want to delete just parts of tables, so I can't use 'TRUNCATE'. As I want to delete about millions of lines, I need a very big rollback segment. The best way would be to delete without using...
1
by: Susan Lam | last post by:
Hi, I am an Oracle developer and new to SqlServer. Is there rollback segment in SqlServer? If there is, does it use only for the purpose of rollback or to maintain read consistency as well? ...
0
by: minapatel | last post by:
I have the following procedure CREATE OR REPLACE procedure FOL_PURGE_CASES as /* type "SET SERVEROUTPUT ON" in sqlplus to debug !! */ cursor all_cases is cursor all_cases is ...
0
by: kinglalit | last post by:
Hi, My DBA wants to turn off the explicit rollback segments currently being used and replace them by UNDO segment. I have explicit mention of these rollback segments in my C++ and Pro*C codes....
1
by: julietmachiwa | last post by:
I ran a troubled script on rollback segment and the whole rollback segment is returned as being full. How do I correct this>
1
by: julietmachiwa | last post by:
I ran a troubled script on rollback segment ans is reutrned as being full. How do I correct this ?
5
by: tkestell | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.