Solved

is it possible to disable rollback segment in oracle?

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

710 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