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

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.

Please create a username to comment.