Adding multiple files to a tablespace

oracletablespaces

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?

Best Answer

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
Related Question