Yesterday I got some alarms about a tablespace running out of free space, closer look revealed that those were false alarms and there was still enough free space to allow segments grow. Probably that is a well known thing for you, but I didn’t know that “bytes” in DBA_DATA_FILES can get larger then “maxbytes”. That was the case, the script was checking “maxbytes” for autoextensible datafiles despite the fact that the real size (“bytes”) is larger. Continue reading to find out how one can get into this kind of situation.

The scenario is quite simple, create an autoextensible data file with maxsize X and then resize the file to Y (make sure Y > X). Here’s the sample!

SQL> create tablespace TTBS datafile '/u01/oradata/MEL1/TTBS.dbf' size 10M autoextend on next 1M maxsize 20M;

Tablespace created.

SQL> column file_name format a26
SQL> select file_name, bytes, autoextensible, maxbytes
 2    from dba_data_files
 3   where tablespace_name = 'TTBS';

FILE_NAME                       BYTES AUT   MAXBYTES
-------------------------- ---------- --- ----------
/u01/oradata/MEL1/TTBS.dbf   10485760 YES   20971520

SQL> alter database datafile '/u01/oradata/MEL1/TTBS.dbf' resize 30M;

Database altered.

SQL> select file_name, bytes, autoextensible, maxbytes
 2    from dba_data_files
 3   where tablespace_name = 'TTBS';

FILE_NAME                       BYTES AUT   MAXBYTES
-------------------------- ---------- --- ----------
/u01/oradata/MEL1/TTBS.dbf   31457280 YES   20971520

I tested this on 10.2.0.5 and 11.2.0.1 and the situation is the same. Check your monitoring tools to see if they handle this situation correctly.