Daily Archives: 16.07.2026

On several customers where databases exists since many years, it is common to find datafile and tablespace management to be inconsistent, mainly due to historical reasons and the DBA preferences at the time.

It is common to see a mix of these patterns in a typical 8192 block size database using smallsize tablespaces:

1. Datafiles of fixed sized, and DBA increases every few months (or when an alert comes) the size by some GB
2. Datafiles near maxsize, for instance 30GB or 32000M, and the DBA adds new datafiles this new size with autoextend off
3. Datafiles near maxsize, for instance 30GB or 32000M, and the DBA adds new datafiles with small size but autoextend on
4. Datafiles with real maxsize and autoextend on

I try always to educate that we should monitor mainly the disk space, not tablespace size. We can monitor the growth, but I don’t want to wake anyone at night because of the tablespace is getting full.

Today I’ve just fixed on the customer databases all the tablespaces, setting all datafiles with autoextend on and maxsize unlimited. It is important to make sure there is a disk space monitor behind it.

To check which tablespaces are impacted by not having the real datafile maximum size, this query can help:

select tablespace_name, round(sum(maxbytes)/1024/1024/1024) current_max_gb, round(sum((power(2,22)-2)-maxblocks)*sum(maxbytes)/sum(maxblocks)/1024/1024/1024) to_possible_max_gb 
from dba_data_files 
where maxblocks!=(power(2,22)-2) 
    or autoextensible!='YES' 
group by tablespace_name 
order by 1;

Here the code to fix this:

begin 
  for df in (select file_id from dba_data_files where maxblocks!=(power(2,22)-2) or autoextensible!='YES') loop
    execute immediate 'alter database datafile '||df.file_id||' autoextend on maxsize unlimited';
  end loop;
end;
/

For smallsize tablespaces, the maximum number of blocks is power(2,22)-2. On internet, it is sometimes written power(2,22)-1, however, when looking at dba_data_files.maxblocks for a unlimited size datafile, the number corresponds to power(2,22)-2.

Ah, for creating and adding datafiles to tablespaces, this is what I use, so it is automatically with autoextend on and unlimited datafile size – both are default:

CREATE TABLESPACE tbs_name;
ALTER TABLESPACE tbs_name ADD DATAFILE;

It expects the usage of OMF – db_create_file_dest parameter is set.

Even better is when OEM Corrective Action is configured correctly, as it takes care of adding datafiles automatically.