ORA-30036

I was recently using an Oracle 9 database. It had an undo tablespace with 1 datafile, which was set to autoextend. However, it could not do so as the file system it was on was full. This produced an ORA-30036 error. I decided to demonstrate this on Oracle 11.1.0.6.0 running on Windows XP. First I created a 500k undo tablespace. By the time I ran the SQL below, it had grown to 647168 bytes:

SQL> l
  1  select file_name, bytes, autoextensible
  2  from dba_data_files
  3* where tablespace_name = 'UNDOTBS2'
SQL> /

FILE_NAME                 BYTES AUT
-------------------- ---------- ---
A:\UNDOTBS2.DBF          647168 YES

SQL>

Notice that it is on the A: drive. This is the 3.5” floppy disk drive (remember them?). It contained a 720k disk. I did this on purpose to stop the undo tablespace growing any bigger than that. I told the database to use this new undo tablespace:

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL>

Then I tried to create a table but Oracle returned an ORA-30036:

SQL> create table fred
  2  as select * from dba_tables
  3  /
as select * from dba_tables
                 *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

SQL>

The following errors appeared in the alert log:

Sun Sep 09 23:43:01 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file c:\app\andrew\diag\rdbms\orcl\orcl\trace\orcl_j003_2916.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
Sun Sep 09 23:44:48 2012
Thread 1 advanced to log sequence 37
  Current log# 1 seq# 37 mem# 0: C:\APP\ANDREW\ORADATA\ORCL\REDO01.LOG
Mon Sep 10 00:06:16 2012
ORA-1652: unable to extend temp segment by 8 in tablespace                 UNDOTBS2 
Mon Sep 10 00:13:05 2012

And when I looked at the floppy disk in Windows, it was full:

If this happens to you, you need to add an extra datafile to the undo tablespace (on a file system with enough free space). Alternatively, you can extend the file system, although in this case it was not possible as a floppy disk has a finite size.