Problem solve Get help with specific problems with your technologies, process and projects.

Running large inserts without rollback errors

I have a question on 'How to run multiple batch jobs using different rollbacks' in a data warehouse environment....

I have more than one large insert (average 5 million rows) batch job that I would like to run at the same time and avoid rollback errors.

I think you're asking how to point a long-running transaction to a specified large rollback segment. All you need to do is to create a large rollback segment, bring it online and set your insert transaction to point to that rollback segment. You'll use SET TRANSACTION USE ROLLBACK SEGMENT REAL_BIG_RBS just before your insert to point the insert transaction to the big rollback segment you created.

To determine which rollback segment a transaction is using at any given time, you can use this query to make sure your INSERT is using the correct RBS:

SELECT r.name rollback_seg_name, l.sid oracle_pid, p.spid system_pid,
       nvl(p.username,'NO TRANSACTION') username, p.terminal
  FROM v$lock l, v$process p, v$rollname r
 WHERE l.sid = p.pid(+)
   AND trunc(l.id1(+)/65536) = r.usn
   AND l.type(+) = 'TX'
   AND l.lmode(+) = 6
 ORDER BY r.name ;

For More Information

This was last published in February 2003

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.