Adding multiple files to a tablespace

Question

In Oracle, we have huge amount of data imported daily, which requires us to create data files off and on in a tablespace.

Wonder if we create two data files simultaneously, will it use both when one is full? Or it will use only latest one?

Answer 1

The algorithms used to allocate space in different datafiles in a single tablespace are not documented except in the database administration guide where it just says:

the database can allocate extents for a segment in any data file in the tablespace

It can change between two releases but it is likely based on same round robin algorithm.

Here is a short demo with Oracle 19 which shows that some round robin algorithm is used:

SQL> select banner from v$version where rownum=1;

BANNER
------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> create tablespace test;

Tablespace created.

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> select tablespace_name, file_id, file_name from dba_data_files where tablespace_name='TEST';

TABLESPACE_NAME     FILE_ID FILE_NAME
-------------------- ---------- ------------------------------------------------------------
TEST                120 /u02/oradata/CDB2/datafile/o1_mf_test_hhxspzt3_.dbf
TEST                121 /u02/oradata/CDB2/datafile/o1_mf_test_hhxsq112_.dbf
TEST                122 /u02/oradata/CDB2/datafile/o1_mf_test_hhxsq2kt_.dbf

SQL> --
SQL> create table t tablespace test as select * from dba_objects ;

Table created.

SQL> select distinct file_id
  2  from dba_extents
  3  where segment_name='T'
  4  and   segment_type='TABLE'
  5  and tablespace_name='TEST';

   FILE_ID
----------
       121
       120
       122