Solved

is it possible to disable rollback segment in oracle?

Posted on 2006-10-27
4
1,707 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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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