I have a question on 'How to run multiple batch jobs using different rollbacks' in a data warehouse environment....
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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
- 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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.