8 Replies Latest reply: May 4, 2012 9:35 AM by sb92075 RSS

    Undo Tablespace Problem causing Production SQL Load to fail!

      Hello forum experts,

      I'm facing a very typical problem which has been highly escalated by client side.
      One of the dataload ,which always executes successfully in the UAT (pre production)environment database with same data volume(around 240 million records),is failing in the production environment-with the following Error log:

      ORA-12801: error signaled in parallel query server P010, instance
      dyl08483dat01:scrprd1 (1)
      ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS1'
      ORA-06512: at "SCARS_REP.PKG_SCARS_PUBLISHED_FACT_POP", line 1081

      We have increased the size of the Undo tablespace from 50GB to 60GB .But load failed again.All the database parameters in both production and UAT environment are same-even the data volume in UAT is an exact replica in Production.But in UAT there is absolutely no problem.I'm really at a loss here to understand what the problem might be and would really appreciate any help in this regard.

      Oracle 11g Enterprise Edition 64 bit :

      Here is the bit of code that is being called while the Undo table space is failing.
      insert /*+ Append */ into PUBLISHED_FACT_MKT_PROD_TMP
      where SYSTEM_VERSION_KEY=v_ver;
      Data in DRAFT_FACT_MARKET_PROD_REVENUE is above 225 million.There are lot of insert statements before and after this step.The stored proc is being called by an Unix script.This is the setup in a nutshell.

      I guess the information provided is not sufficient but I'm not sure what more info to provide.Please help!

      Many thanks.

      Edited by: user1128836 on May 4, 2012 3:52 AM

      Edited by: user1128836 on May 4, 2012 3:53 AM