Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

is it possible to disable rollback segment in oracle?

Posted on 2006-10-27
4
Medium Priority
?
1,790 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
  • 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

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
Suggested Courses

783 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