10 Replies Latest reply: Sep 10, 2012 5:54 AM by user12133490 RSS

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

    631285
      Hi experts,
      I'm getting this error
      "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'"
      will you please tell me the reason and suggest me, what to do.
      OS - SunOS 5.10
      oracle - 10.2.0.3.0
        • 1. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
          641802
          I think ur Undo tablespace UNDOTBS1, is full!
          --U can start by checking the querries running on ur database and try to optimize them.
          --Increase the size of UNDOTBS1,
          --Check the Undo retention parameter.....
          U should start reading abt Managing Undo Tablespace!

          cheers!!!!!
          • 3. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
            631285
            I can see my undo tablespace having enough free space. process was running from 9 hrs , in this period , undo tablespace was 99% full for some time, but after that it was free.
            • 4. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
              829019
              you are trying to Insert more numbers of data into a table than it can hold at a time.
              Solutions can be:-

              1:-Just adjust the constraint in such a way that the total numbers of data inserted is less; do the required action and again insert the rest numbers of data in second run.. This way u can use same table for activity but in two separate runs.

              2:-create two (or more in case of very large numbers of data) temporary tables ; insert whole data part wise ; do the action on one temporary table at a time ; and at the end, delete the temporary table.

              3:-Increase the size of tablespace of table in which you want to insert; and all data can be flown in that, in one go.
              • 5. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
                Uwehesse-Oracle
                >
                I can see my undo tablespace having enough free space. process was running from 9 hrs , in this period , undo tablespace was 99% full for some time, but after that it was free.
                >

                There is not much room left to argue here. Your error message doesn't lie: There was DML that needed to place Before Images in the Undo Tablespace and it was no more space left there. So the DML was rolled back and returned an error. Your options:

                a) Ignore the error and have DML on your Database that can't get through
                b) Increase the datafiles of the Undo Tablespace or allow autoextending (with reasonable MAXSIZE)

                I'd opt for b, if you ask me :-)

                Kind regards
                Uwe Hesse

                http://uhesse.wordpress.com
                • 6. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
                  Uwehesse-Oracle
                  >
                  you are trying to Insert more numbers of data into a table than it can hold at a time.
                  Solutions can be:-

                  1:-Just adjust the constraint in such a way that the total numbers of data inserted is less; do the required action and again insert the rest numbers of data in second run.. This way u can use same table for activity but in two separate runs.

                  2:-create two (or more in case of very large numbers of data) temporary tables ; insert whole data part wise ; do the action on one temporary table at a time ; and at the end, delete the temporary table.

                  3:-Increase the size of tablespace of table in which you want to insert; and all data can be flown in that, in one go.
                  >

                  I am sorry to say this, but you are not giving helpful advice because you did not understand the problem.

                  It is not the table that maybe gets inserts that has no more space to grow . The space problem is in the Undo Tablespace. Therefore your suggestion would not resolve the problem at all but instead lead to high unnecessary effort.

                  Kind regards
                  Uwe Hesse

                  http://uhesse.wordpress.com
                  • 7. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
                    790073
                    Hi, user628282,

                    Actually your undo tablespace got full. So, you are getting the error message.

                    Here there are two options you can try:

                    1. You can resize your undo tablespace size. If you have enough space.

                    E.g., Login as sys dba.And use below command.

                    SQL> alter database datafile ‘/u02/oracle/test/undo_tbs.dbf’ resize 700M;

                    2. Or else you can add datafile in your undo tablespace. If you have enough space.

                    E.g.,Login as sysdba. and use the below command.

                    SQL>create undo tablespace myundo datafile ‘/u02/oracle/test/undo_tbs.dbf’ size 500M autoextend ON next 5M;


                    Best Regards,
                    Vinod Ranjan
                    • 8. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
                      790073
                      Or you can wait untill active transaction to commit.
                      • 9. ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
                        848931
                        Hallo.

                        I have this problem 30036 after this request (ver. 10.2):

                        DELETE FROM AUDITDBA.SEOSDATA where COMPUTERNAME ='xxxxx' AND TIMSTAMP >= '18-03-2011'    (! number of rows is less as 2 000 000)


                        I tried do this:

                        ALTER SYSTEM SET UNDO_RETENTION = 5;

                        But after next action "DELETE" again problem 30036..

                        Here is a "space status" from the system:

                        TABLESPACE_NAME VOLNE_MIESTO
                        ------------------------------ -------------------------------------------
                        CA_AUDIT 3714,1875 MB
                        SYSAUX 42,1875 MB
                        SYSTEM 5,25 MB
                        UNDO_TEMP 7998,0625 MB
                        USERS 999,125 MB

                        It's clear for me that datafile UNDOTBS is small but I have same questions:

                        1. What You recommend - use autoextend on for the UNDOTBS datafile or extend it manualy?

                        2. What size You recommend for the UNDOTBS when I have DB size cca 350 GB, into DB incomming cca 50 events (rows) per second and I need remove from this DB aproximetly 1/2 numbers of rows (old data)

                        3. Recommend me best way how I can remove old data from DB without problems as "30036"...


                        Thank You in advance


                        Peter
                        • 10. Re: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
                          user12133490
                          If you get this error it could well be that:

                          (1) Your undo tablespace is set to autoextend
                          AND
                          (2) The file system it is on is full.

                          You can see what I mean here:

                          http://international-dba.blogspot.co.uk/2012/09/ora-30036.html