UNDO tablespace cannot support transaction
Hi, Due to my work environment, I'm not allowed to cut and paste from my environment. As a result of that I cannot provide a specific test case, but just gives symptoms, would you give me general guidance on how to approach this problem? I'm running a huge insert 10878201 rows, and each record is quite fat, it has about 50 or 60 columns. The query was issued from Toad and the connection handle was lost because Toad froze and then when restarted the same connection handle was lost. However, I did a DBMS_SESSION trace enable on the sid, serial#, and got a lot of UNDO segment info in the trace file and then finally it says in the trace: Problem: Undo tablespace cannot support required UNDO retention Recommendation: Size UNDO tablespace to 3702 MB However, there are 11 datafiles (32G * 11) in the UNDO and AFAIK my (Insert into SELECT * from huge table) was the only Active USER transaction that day (4 days back). Somehow the tablespace allocated could have easily supported the UNDO_RETENTION (28800). REDO Logs: There are 4 log groups (2 per thread), each is 200MB, this particular transaction is running on thread 1 of RAC node1. 1) Shall I try to split the transactions into manageable size chunks and commit every 10000 rows or so? 2) Will resizing the REDO log size help? Adding significant info to this question: I get a "ORA-30036" in the trace file generated about 4days back when Toad froze. However, the session still shows up as ACTIVE in gv$session and last_call_et keeps on inreasing. I hope this added info will help Thanks
UNDO tablespace cannot support transaction