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 5 12869
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..
Varol,
Thanks very much...I'll talk to DBA about using a bigger segment with
SET TRANS...
Thanks again
Tomas
"varol cavdar" <va***@bilisi m-ltd.com.tr> wrote in message
news:e0******** *************** ***@posting.goo gle.com... 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..
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
"tkestell" <tk******@attbi .com> wrote in message news:<lSPKa.288 22$3d.17163@scc rnsc02>... 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 --
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.googl e.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
allocates 100 MB ?
Does this mean that if there are two transactions using this rollback
|
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 into the normal rollbacks created and gave an error of "maxextents
for rollback reached", so our DBA created a rollback of 500 MB and I used
this...
|
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 rollback
because the delete really takes a lot of time copying all data into
the rollback file. Is this possible with oracle?
Many thanks for any...
|
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?
If there is no rbs in SqlServer. How does it handle rollback and read
consistency?
Thanks!
|
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
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
| |
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. What changes am I supposed to make in order to implement the UNDO segements. Do I need to remove the function or only commenting the rollback segment...
|
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>
|
by: julietmachiwa |
last post by:
I ran a troubled script on rollback segment ans is reutrned as being full.
How do I correct this ?
|
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 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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |