How to Resolve ORA-30036 UNABLE TO EXTEND SEGMENT BY 8 IN UNDO TABLESPACE 'UNDOTBS1'

ORA-30036

This error ORA-30036 could be very serious in a situation that is described below and may make users panic:

One day, a DBA was importing data and waited it for a long time. He suspected the operation was hanged, so he tried to cancel the operation by interrupting the job, but it didn't work. After he killed the server process of importing job on OS-level, all users were hanged. He made a decision to shutdown abort the database. As of he startup the database, the story began with the error message:

...
Sat May 12 19:04:27 2010
Errors in file D:\oracle\admin\dbname\udump\dbname_ora_2154.trc:
ORA-30036 UNABLE TO EXTEND SEGMENT BY 8 IN UNDO TABLESPACE 'UNDOTBS1'
...

There're two symptoms of ORA-30036 in above case that we should notice:

1. Database hanged
2. Database refused to startup

ORA-30036 could be severe to prevent the database from startup just like our case above. Let's see what we can get in the content of the error:

Description

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

Cause

the specified undo tablespace has no more space available.

Action

Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

Before you follow "Action", the first thing you must do is to open the database. In our case, we can't even open the database, how can we add more space to the undo tablespace?

Solutions

You can choose one of the following solutions to open your database:

1. Set initialization parameter UNDO_MANAGEMENT to MANUAL: It's to help the database escape from the confinement of the default undo tablespace.
2. Set initialization parameter UNDO_RETENTION to a small value: It's to signal the database to discard expired undo data.

Steps for MANUAL UNDO_MANAGEMENT

The above two solutions against ORA-30036 all work. Let's see the first solution:

1. Mount database.
SQL> STARTUP MOUNT;
2. Enable manual undo management.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='MANUAL' SCOPE=SPFILE;
3. Restart database.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
4. Add a datafile to undo tablespace (or resize the undo datafile).
SQL> ALTER TABLESPACE undotbs1 ADD DATAFILE '/path_to_datafile/datafilename.dbf' SIZE 2G;

Further reading: How Big a Data File Can Be?

5. Enable automatic undo management.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
6. Restart database.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

If you use a modified pfile to open database, please skip the step 1, 2 and 5, and use the following statement to replace step 3

SQL> STARTUP OPEN PFILE='?/dbs/init<sid>.ora';

Steps for Smaller UNDO_RETENTION

In the second solution, we use a pfile to open the database:

1. Make sure the pfile contains UNDO_RETENTION.
...
UNDO_RETENTION=1
...
2. Open the database with a modified pfile.
SQL> STARTUP OPEN PFILE='?/dbs/init<sid>.ora';
3. Make sure the space is freed.
SQL> SELECT SUM(bytes)/1024/1024 "Free Space (MB)" FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS1';
4. Restart database, don't use pfile to open.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Preventive Actions to ORA-30036

As a DBA, we should always think about how to prevent various errors including ORA-30036. There are several techniques to prevent this error when you import a chunk of data.

1. Disable retention guarantee temporarily.

Retention guarantee is working well under normal conditions, but if you are going to import or load bulk data, there will be a lot of undo data generated, it's beyond normal expectation. So you may disable retention guarantee before import or bulk loading, just temporarily.

Now, let's check the retention type first.

SQL> SELECT retention FROM dba_tablespaces WHERE tablespace_name='UNDOTBS1';

If it's GUARANTEE, please change it to NOGUARANTEE.

SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

For more clear explanation about the relationship betweenUNDO_RETENTION and RETENTION GUARANTEE, you may check this post: UNDO_RETENTION, How and Why

2. Decrease UNDO_RETENTION to a smaller value temporarily.

It's not always a good idea, but it will work when your undo retention is currently a large value. For example, 21,600 seconds in this case. You can decrease the value to a smaller value, say 3600 seconds to age out undo data more quickly. Eventually, it will reduce space pressure that solves ORA-30036.

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
3. Enable AUTOEXTEND.

We have to check the tablespace type first.

SQL> SELECT bigfile FROM v$tablespace WHERE name = 'UNDOTBS1';

If the tablespace is created with bigfile, we can enable AUTOEXTEND on tablespace-level.

SQL> ALTER TABLESPACE UNDOTBS1 AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Otherwise, you can do it on datafile-level.

SQL> ALTER DATABASE DATAFILE '/path_to_datafile/datafilename.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

As you can see, the whole concepts of UNDO is pretty complicated and challenging for DBA to understand. That's why we use this long post to explain ORA-30036, an UNDO related error.