1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

facing ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TS'

Discussion in 'SQL PL/SQL' started by laxman, Jan 27, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i am trying to update one column of table which is having 26371642 records.The update is not taking place and after few hours i am oserving one oracle error mentioned below.

    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TS'


    Any suggestion will be highly appreciated.

    Regards
    Laxman
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    As the error suggests, it is a problem with Undo table space

    You can either do one of the following things.

    1. Check the size of Undo table space. it it is small. increase the size.
    The Query to find the size of Undo table space is

    Code (SQL):

    SELECT SUM(bytes)/1024/1024  Undo_Size_MB
      FROM dba_data_files
     WHERE tablespace_name = 'UNDO_TS';
     
    Check for
    Undo Management
    more UNDO table space operations

    2. Basically Huge transaction may lead to this error. So it is advisable to break transaction to smaller units by usting Bulk collect and updates.

    please refer Bulk Collect and DML .
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks sir, for your valuable information