ORA-30036: Unable to extend segment by %s in undo tablespace '%s'
There is not enough space left either due to the datafiles being full, autoextend which is not set at datafile level or due to a disk which's full.
You'll have to check the size of the datafiles attached to the undo tablespace and check whether they can autoextend or not..
select file_name, bytes, autoextensible, maxbytesEither add more datafiles to the undo tablespace, set the autoextensible flag or enlarge the datafile(s).
from dba_data_files
where tablespace_name='here UNDO TABLESPACE_NAME'
To add more space to a file issue following command:
alter database datafile 'C:\ORACLE\ORADATA\UNDO01.DBF' resize 1000m;To turn on the autoextend feature on a datafile use following command:
alter database datafile 'C:\ORACLE\ORADATA\UNDO01.DBF' autoextend on next 100m maxsize 2000m;To add a new datafile to the undo tablespace use following command:
alter tablespace UNDOTBS01 add datafile 'C:\ORACLE\ORADATA\UNDOTBS02.DBF' size 100m autoextend on next 100m maxsize 2000m;
Welcome to our forum for Oracle error: ORA-30036 Add your own message
We are facing problem while running a process called as "MOVE TO HISTORY".This process tranfers data from standard table to history table in bulk.
When the first time this process is executed it is giving error "Unable to extend segment by8 in UNDO tablespace 'UNDOTBS1'"
After this we increased the UNDO tablespace to 2GB and try to execute the process again bt again we got the same error.
Then we again increased the space and now it is 5GB and also now there is no maxbytes, no autoextensible but now again we are getting the same error.
Cau you please tell why we are getting this error and is there is any other way to solve this problem
Kind Regards,
Tannu Sahay
A move operation can generate a lot of UNDO information, specially if the tables contain triggers which insert some audit info into other tables, if there are a lot of depending (child) tables involved, ...
It's difficult to tell how much UNDO the move operation needs.
What you can do is to monitor the transaction in v$transaction for the session the move operation is taking place, and interpolate the space needed for the amount of records that are being moved.
this error has occured.. but after 5 minutes the error got resolved automatically.
why do it happens..wht the solution to fix this to avoid in the future.
I know this post has been years ago but I want to answer for the other people out there. The reason why it got resolved in 5 minutes is because your undo tablespace 'UNDOTBS1' already autoextended.
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.