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

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!!!!!

604693

add more space

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.

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.

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

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

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

790073

Or you can wait untill active transaction to commit.

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

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