Can an autoextensible data file grow bigger then dba_data_files.maxbytes? Sure!
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.