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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top