I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace –
- will the first datafile continue to be used until it reaches full capacity and only then will the second one start to be used for data or
- the first datafile will forever remain only 50% used as the data changes are written to the new datafile or
- something else happens which I haven’t thought of?
I need this info to plan a strategy for dealing with increasing the size of tablespaces without wasting physical space.
In general, extents are allocated in a round-robin fashion among all data files in a tablespace so long as those data files have enough free space to allocate the extent. So if you add a new data file, you would generally expect that half of the new extents would be allocated in the existing data file and half of the new extents would be allocated in the new data file. Assuming the two data files are the same size, you would generally expect that by the time the first data file got to 100% full, the second data file would be roughly 50% full.
I’m not sure, though, how this helps you deal with increasing tablespace size without wasting physical space. I’m hard-pressed to imagine in what circumstances knowing how extents are going to be allocated is going to help you use less physical space. It shouldn’t matter from that perspective whether you double the size of the existing data file, add a new data file, or add multiple data files and it shouldn’t matter whether one data file is filled up before the next one starts being used. In any case, you’ve allocated the same amount of space at the operating system level, you’ve allocated the same number of extents in the tablespace, and your segments are all the same size.
You would decrease the amount of space allocated at the operating system level by letting the data files autoextend rather than allocating fixed size data files but then you have to monitor the free space at the operating system level to ensure that the data files all have enough space to grow and you may complicate your ability to move data files to different mountpoints. You may also decrease the amount of parallelism you can get in your backups forcing the backup job to run a bit longer.