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?

  • Why tablespace? You can create data files.. – Popeye Jul 3 '20 at 6:54
  • Yeah, I mean data files. Does it uses only latest one? – mrd Jul 3 '20 at 6:55
  • What does "two tablespace (data files)" mean? A tablespace can have any number of files. You can add files to a tablespace or set it to auto-extend. You don't add a tablespace to increase space for new data. – William Robertson Jul 3 '20 at 7:19

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;

Oracle Database 19c Enterprise Edition Release - 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';

-------------------- ---------- ------------------------------------------------------------
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';


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy