Ask the Expert

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.

    Requires Free Membership to View

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 first published in February 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: