Are there triggers on the table that cause insert/update/delete on other tables? This would generate undo as well.There might be other activity in the database that generates undo (statspack comes to mind). You will need to track the info from v$transation
Ram Raman wrote:
Thanks for your replies.1. We are not doing multiple commits. Just one INSERT INTO SELECT statement with one commit at the end. There were no other transactions when we started this one. 2. The space for both data and index is about 5.5Gb. Last time we ran the process, the 30GB UNDO TBS was close to 100% free when we started the process and the UNDO TBS became 100% full and then erred out because of lack of space. Why would the undo TBS grow out to be almost 30GB causing the process to fail esp. when no other process is running in the database. The only other active processes are Oracle background processes. Oracle is 9206. We do have the option of using Direct path insert and turning the logging off, but this behavior is perplexing.