Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

is it possible to disable rollback segment in oracle?

Posted on 2006-10-27
4
Medium Priority
?
1,757 Views
Last Modified: 2012-06-21
Hi! I'm facing a terrible and urgent problem.
I'm am in production enviroment and I have a tool to archive data contained in my database instance, Oracle 9i.
The problem is that, due to huge amount of data, I should disable rollback segment, or I can not perform the operation.
How can I do that working only on database configuration or table attributes?
Thanks in advance!
0
Comment
Question by:hanoirules
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 2

Expert Comment

by:Tayger
ID: 17820820
Hello

If you can do the transaction in a PL/SQL procedure you could write a routine that sets a commit point like after every 100k inserted records or so. This should keep the rollback segment smaller.
0
 
LVL 2

Expert Comment

by:Tayger
ID: 17822172
I found another interesting hint for optimizing big inserts. The hint is called "append": When you use the APPEND hint for INSERT, data is simply appended to a table above the HWM which has the effect of not creating UNDO. Existing free space in blocks is not used.
You can use it that way:
insert /*+ APPEND */ into <target_table>
SELECT * FROM <source_table(s)>;

Additionally you can also use the Nologging option to minimize the amount of redo's.
0
 

Author Comment

by:hanoirules
ID: 17835715
Thanks to everybody.
Unfortunally I can not touch the PL/SQL code..
Thanks anyway
0
 
LVL 2

Accepted Solution

by:
Tayger earned 2000 total points
ID: 17836203
I was discussing today with a DBA and he told me that its not possible to deactivate the rollback segment. Its just one part of doing a proper transaction and thats how Oracle DB works. He also says you have to use the APPEND hint then. It also will fill the rollback segment but with much less data.
I dont know the circumstances you have to work for your client but even if it would be possible to to disable the rollback segment it would be a much more dangerous operation that just adding the hint option into the insert statement, especially you wont change the existing data, you only want to archive them. Btw archiving... I dont know the tool you are working with but RMAN (from Oracle) should do this job properly, otherwise you can use the export function.

Im sorry my answer was not satisfying but at least you know now you cant disable the rollback segment.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question