Running large inserts without 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

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.