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.
Hi Maris,
I have few scripts that check maxbytes for autoextensible datafiles and till now I’ve noticed this issue few times – also was confused a little when I first saw alarms.
“Bytes” can become larger then “maxbytes” only if DBA alters that datafile. Some DBA’s ignore “maxbytes” as they tend to resize datafile when there is insufficient disk space.
Thank you for noticing this – I simply forgot about this issue… It’s time to correct some scripts.
Hi,
I handled the situation by changing the “maxbytes” to “greatest(maxbytes,bytes)” in the part of the script which check the maximum size of the datafile. I think that should be enough to cope with this situation.
Thanks for the feedback,
Maris
Hi Maurice,
Just greatest(maxbytes,bytes) will not solve the problem. It may so happen that maxbytes is greater than bytes but autoextensible is OFF. I’ve written a query for this purpose, which should work for most of the scenario’s. Please feel free to you test & validate.
https://oraworklog.wordpress.com/2010/02/23/free-space-in-auto-extensible-tablespaces/
Thanks,
Ravi.M
Hi Ravi,
That’s the first time my name got written as you did it
Do you know how to get into the situation you describe? I tested disabling autoextend of a datafile on the same 10.2.0.5 and 11.2.0.1 versions and it sets maxbytes to “0″, but for safety you could/should write something like “decode(autoextensible,’YES’,greatest(bytes,maxbytes),bytes)”
Maris
Hi Maris,
Sorry. I have another friend you names spells like that. I meant no offense. Unfortunately I cannot edit my comment.
I didn’t test it in either 10.2.0.5 or 11.2.0.1 . But I remember facing that issue. Let me get back to you with test case.
Thanks,
Ravi.M
It is expected behavior although not logically right to me. From the documentation: “The value of MAXSIZE is the maximum size to which the file can automatically extend.” Manually, we can resize above what maxsize is.
I find your blog very good and down to earth.