Forum Stats

  • 3,826,643 Users
  • 2,260,684 Discussions
  • 7,897,044 Comments

Discussions

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

631285
631285 Member Posts: 34
edited Sep 10, 2012 6:54AM in General Database Discussions
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
a9103f8b-974a-4780-9d25-4679b869416b

Comments

  • 641802
    641802 Member Posts: 92
    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!!!!!
  • 604693
    604693 Member Posts: 541
    add more space
  • 631285
    631285 Member Posts: 34
    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.
  • 829019
    829019 Member Posts: 5
    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.
    a9103f8b-974a-4780-9d25-4679b869416b
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    >
    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
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    >
    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
  • 790073
    790073 Member Posts: 72
    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
  • 790073
    790073 Member Posts: 72
    Or you can wait untill active transaction to commit.
  • 848931
    848931 Member Posts: 1
    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
  • 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
This discussion has been closed.