Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

631285May 29 2008 — edited Sep 10 2012
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

Comments

641802 May 29 2008
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 May 29 2008
add more space
631285 May 29 2008
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 Jan 6 2011
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.
Uwehesse-Oracle Jan 6 2011
>
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 Jan 6 2011
>
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 Jan 6 2011
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 Jan 6 2011
Or you can wait untill active transaction to commit.
848931 Mar 21 2011
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
user12133490 Sep 10 2012
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
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 8 2012
Added on May 29 2008
10 comments
91,052 views