Can You Set a Datafile's Maxbytes / Maxsize to Less Than its Actual Size?

I was dealing with a message like this for one of our developers:

ORA-01653: unable to extend table ...

He was using a tablespace which had one datafile with AUTOEXTEND ON and this datafile had reached its MAXBYTES / MAXSIZE. I wondered what would happen if you tried to set a datafile's MAXBYTES / MAXSIZE to a value less than its actual size. I tried this first on Oracle 9. I started with a datafile which had an actual size of 20 megabytes and a MAXBYTES / MAXSIZE of 50 megabytes. I found that I was able to set its MAXBYTES / MAXSIZE to 1 megabyte and that the change was recorded in DBA_DATA_FILES:

SQL> select file_id, bytes, maxbytes, autoextensible

  2  from dba_data_files

  3  where tablespace_name = 'USER_DATA'

  4  /

 

   FILE_ID      BYTES   MAXBYTES AUT

---------- ---------- ---------- ---

        22   20971520   52428800 YES

 

SQL> alter database datafile 22

  2  autoextend on

  3  maxsize 1m

  4  /

 

Database altered.

 

SQL> select file_id, bytes, maxbytes, autoextensible

  2  from dba_data_files

  3  where tablespace_name = 'USER_DATA'

  4  /

 

   FILE_ID      BYTES   MAXBYTES AUT

---------- ---------- ---------- ---

        22   20971520    1048576 YES

 

SQL>

However, when I tried to do a similar thing in Oracle 11, the MAXBYTES value shown in DBA_DATA_FILES did not go below the actual size of the datafile:

SQL> select file_id, bytes, maxbytes

  2  from dba_data_files

  3  where tablespace_name = 'USERS'

  4  /

 

   FILE_ID      BYTES   MAXBYTES

---------- ---------- ----------

         4   20971520   52428800

 

SQL> alter database datafile 4

  2  autoextend on maxsize 10m

  3  /

 

Database altered.

 

SQL> select file_id, bytes, maxbytes

  2  from dba_data_files

  3  where tablespace_name = 'USERS'

  4  /

 

   FILE_ID      BYTES   MAXBYTES

---------- ---------- ----------

         4   20971520   20971520

 

SQL>

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.