is it possible to disable rollback segment in oracle?

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!
hanoirulesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
TaygerConnect With a Mentor Commented:
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
 
TaygerCommented:
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
 
TaygerCommented:
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
 
hanoirulesAuthor Commented:
Thanks to everybody.
Unfortunally I can not touch the PL/SQL code..
Thanks anyway
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.