?
Solved

Regarding SET TRANSACTION USE ROLLBACK SEGMENT rbsbig;

Posted on 2007-10-17
6
Medium Priority
?
2,166 Views
Last Modified: 2012-06-21
Hi,

  I am using this in my pl/sql code. The following line:

SET TRANSACTION USE ROLLBACK SEGMENT rbsbig;

After this line, I am updating a table and then commiting it.

 When i run this package, its throwing this message:

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode 2

What exactly this 'TRANSACTION USE ROLLBACK SEGMENT ' does. Also, what that error message is.

Can anybody explain with an example and how to get rid of this error if possible.

Help Appreciated.

Thanks
0
Comment
Question by:amankhan
5 Comments
 
LVL 9

Accepted Solution

by:
konektor earned 500 total points
ID: 20093528
you cannot use it when you are using automatic undo management
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10005.htm#i2067247
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 20093536
The statement is forcing your transaction to use a specific rollback segment.  Normally Oracle picks one for you.  The rollback (or undo) is your "work space", it's where Oracle stores the information needed to "undo" your work if you don't commit your changes.

If you have a particularly large transaction it's a common practice to allocate a dedicated rollback segment for that process.  In this case your transaction is looking for a segment called "RBSBIG"

But you're not allowed to pick your own segment when your db instance is in Automatic" Undo
you can only do that when your instance is set for "Manual".

To fix, have your dba change the setting and restart the db instance (drastic, I don't recommend)
or
remove the set transaction line, just comment it out  (must less drastic, I DO recommend)
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 500 total points
ID: 20099473
if you decide and need that rollback segment to be set in order for your
following dml to go through, then you have to go with the below :

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
Cause: This operation only allowed in Manual Undo mode.
Action: restart instance in Manual Undo_Management mode and retry operation.

Otherwise just go ahead with commenting that line and try to see if your
dml following that set transaction statement goes fine or not.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 500 total points
ID: 20100866
I agree that it is better to try to comment the statement
SET TRANSACTION USE ROLLBACK SEGMENT rbsbig;
and to see how will the transaction finish.
If everything is OK you do not need it.
The transition to independent UNDO segment will be
recommended if you have heavy load on the DB and can place
the independent UNDO segment on separate disk.
To minimize the space your transaction ocupies in the undo
segment try to 'commit' frequently.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20591412
Forced accept.

Computer101
EE Admin
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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month9 days, 12 hours left to enroll

609 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