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