Home -> Mailing Lists -> Oracle-L -> Re: UNDO Space Error

Re: UNDO Space Error

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 20 Mar 2021 20:55:19 -0400
Message-ID: <495f94ae-3567-1a5c-0813-694ffa65c910_at_gmail.com> 





  
    
  
  
    
As opposed to ORA-01555 which is a nightmare to debug and
      resolve, ORA-30036 doesn't present such problems because it's
      thrown by the same transaction that causes it. The cause is very
      simple: the current transaction ran out of space in the undo
      segment. At this point, I'd like to remind you of the common
      saying that disks are cheap, especially is someone else is buying
      them. So, what you need to do is to figure out which transaction
      is causing the problem. Second thing is figuring out what to do
      about that. I've frequently seen this with data purges.  If you
      want to get rid of 1/3 of the rows in a billion rows table, based
      on a date coliumn, it is conceivable that you might need few KB of
      the UNDO space. The solution is usually to write a PL/SQL
      procedure which will clean the rows in batches of 100K.

    
So, what were you doing? Can you post the SQL which has thrown
      the error? Without the exact statement, cardinality of the tables
      involved and rough estimate how much data are you modifying, it's
      hard to tell you anything more.

    

    
On 3/20/21 2:22 PM, Pap wrote:

    

    

      
      

        
Hello
          Listers, Its version 11.2.0.4 of oracle. We suddenly started
          encountering ORA-30036(ORA-30036: unable to extend segment by
          8 in undo tablespace 'UNDOTBS2') in one of the databases. We
          don't have any changes done to the code. We already increased
          the UNDO tablespace size from ~100Gb to ~190GB but still
          facing the same and this time we want to find the root cause
          rather than keep on increasing the size of tablespace.

        
During
            one of the failures I saw DBA_UNDO_EXTENTS was showing
            almost all of the extent status as UNEXPIRED. dba_free_space
            was showing zero space for that tablespace. We have the data
            files in the undo tablespace set as autoextend ON. And we
            are using AUTO undo management with UNDO retention set as
            900.

        

        
I
            understand there are two types of UNDO noted by oracle , one
            is UNDO read which the SELECT query sometimes fails with
            Ora-01555 (but here we are not encountering that). The other
            one is UNDO generation because of the
            DML(INSERT/UPDATE/DELETE) and in this case our failure is
            because of the same UNDO and each time it's mostly INSERT
            queries failing while doing data load. So is there any way I
            can track the exact session/sql/user which is generating
            maximum UNDO from any historical AWR views and also during
            run time?

        
How
            to debug from history and get the cause of this sudden
            increase in UNDO space consumption? Any
            other fix other than increasing UNDO space?

        
Regards

        
pap

        


        

        


        

      

    

    
-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com


  

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Mar 21 2021 - 01:55:19 CET












Original text of this message