A recurring question I see has to do with the allocation of extents in tablespaces with multiple data files. The question is, does oracle completely fill one data file before beginning to use the next, or does it use the files in a balanced, round-robin fashion? Various resources give conflicting answers. A few years ago I put together a test to discover for myself how Oracle handles this. I’ve pulled that test (which was originally run on Oracle 9.2) out of my archives and tried it again on 11.2. Here are the results and observations along the way.
First, we’ll create a tablespace with three small datafiles, create a table, and populate it to force multiple extents.
SQL> create SMALLFILE tablespace bubba_ts 2 datafile '/ora01/oradata/ORCL/bubbatbs_01.dbf' 3 size 1m 4 autoextend off, 5 '/ora01/oradata/ORCL/bubbatbs_02.dbf' 6 size 1m 7 autoextend off, 8 '/ora01/oradata/ORCL/bubbatbs_03.dbf' 9 size 1m 10 autoextend off 11 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K; Tablespace created. SQL> create user bubba 2 identified by bubbapw 3 default tablespace bubba_ts 4 temporary tablespace temp 5 quota unlimited on bubba_ts; User created. SQL> -- SQL> create table bubba.rowid_test 2 ( 3 key_col number, 4 big_col1 char(2000), 5 big_col2 char(2000), 6 big_col3 char(2000) 7 ); Table created. SQL> -- SQL> BEGIN 2 for i in 1..100 loop 3 insert into bubba.rowid_test 4 values (i, 5 'xxxxx', 6 'xxxxx', 7 'xxxxx' 8 ); 9 end loop; 10 END; 11 / PL/SQL procedure successfully completed.
Fig. 1
Next, we’ll simply look at where each row was created. The list is lengthy, but as you scroll down, it becomes apparent that Oracle used the files in a ’round-robin’ fashion.
SQL> declare
2 r rowid;
3 i number := 1;
4 v_file_name dba_data_files.file_name%type;
5 begin
6
7 for p in (
8 select rowid
9 from bubba.rowid_test
10 ) loop
11
12 select file_name
13 into v_file_name
14 from dba_data_files
15 where file_id = dbms_rowid.rowid_to_absolute_fno(p.rowid, 'BUBBA', 'ROWID_TEST');
16
17
18 dbms_output.put_line('row no: ' || i ||' file : ' || v_file_name);
19
20 i := i+1;
21 end loop;
22 end;
23 /
row no: 1 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 2 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 3 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 4 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 5 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 6 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 7 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 8 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 9 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 10 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 11 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 12 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 13 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 14 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 15 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 16 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 17 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 18 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 19 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 20 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 21 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 22 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 23 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 24 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 25 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 26 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 27 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 28 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 29 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 30 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 31 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 32 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 33 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 34 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 35 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 36 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 37 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 38 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 39 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 40 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 41 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 42 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 43 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 44 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 45 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 46 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 47 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 48 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 49 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 50 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 51 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 52 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 53 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 54 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 55 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 56 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 57 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 58 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 59 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 60 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 61 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 62 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 63 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 64 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 65 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 66 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 67 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 68 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 69 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 70 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 71 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 72 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 73 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 74 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 75 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 76 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 77 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 78 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 79 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 80 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 81 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 82 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 83 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 84 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 85 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 86 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 87 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 88 file : /ora01/oradata/ORCL/bubbatbs_03.dbf
row no: 89 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 90 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 91 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 92 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 93 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 94 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 95 file : /ora01/oradata/ORCL/bubbatbs_01.dbf
row no: 96 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 97 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 98 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 99 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 100 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
PL/SQL procedure successfully completed.
Fig. 2
And finally, another check of simply the extents allocation. Again, it is easy to see the round-robin usage/allocation pattern
SQL> select e.extent_id, 2 e.file_id, 3 f.file_name 4 from dba_extents e, 5 dba_data_files f 6 where e.owner = 'BUBBA' 7 and e.segment_name = 'ROWID_TEST' 8 and e.file_id = f.file_id 9 order by e.extent_id 10 ; EXTENT_ID FILE_ID FILE_NAME ---------- ---------- ----------------------------------- 0 8 /ora01/oradata/ORCL/bubbatbs_01.dbf 1 9 /ora01/oradata/ORCL/bubbatbs_02.dbf 2 10 /ora01/oradata/ORCL/bubbatbs_03.dbf 3 8 /ora01/oradata/ORCL/bubbatbs_01.dbf 4 9 /ora01/oradata/ORCL/bubbatbs_02.dbf 5 10 /ora01/oradata/ORCL/bubbatbs_03.dbf 6 8 /ora01/oradata/ORCL/bubbatbs_01.dbf 7 9 /ora01/oradata/ORCL/bubbatbs_02.dbf 8 10 /ora01/oradata/ORCL/bubbatbs_03.dbf 9 8 /ora01/oradata/ORCL/bubbatbs_01.dbf 10 9 /ora01/oradata/ORCL/bubbatbs_02.dbf 11 10 /ora01/oradata/ORCL/bubbatbs_03.dbf 12 8 /ora01/oradata/ORCL/bubbatbs_01.dbf 13 9 /ora01/oradata/ORCL/bubbatbs_02.dbf 14 rows selected. SQL> --
Fig. 3
However, notice that when I created the tablespace, I used “EXTENT MANAGEMENT LOCAL UNIFORM SIZE”. Let’s change that to “EXTENT MANAGEMENT AUTOALLOCATE” and observe the difference:
SQL> create SMALLFILE tablespace bubba_ts 2 datafile '/ora01/oradata/ORCL/bubbatbs_01.dbf' 3 size 1m 4 autoextend off, 5 '/ora01/oradata/ORCL/bubbatbs_02.dbf' 6 size 1m 7 autoextend off, 8 '/ora01/oradata/ORCL/bubbatbs_03.dbf' 9 size 1m 10 autoextend off 11 EXTENT MANAGEMENT LOCAL autoallocate; Tablespace created.
Fig. 4
After repeating the same data load procedure as before, the queries to see the usage pattern now show we’ve only used one datafile:
SQL> declare
2 r rowid;
3 i number := 1;
4 v_file_name dba_data_files.file_name%type;
5 begin
6
7 for p in (
8 select rowid
9 from bubba.rowid_test
10 ) loop
11
12 select file_name
13 into v_file_name
14 from dba_data_files
15 where file_id = dbms_rowid.rowid_to_absolute_fno(p.rowid, 'BUBBA', 'ROWID_TEST');
16
17
18 dbms_output.put_line('row no: ' || i ||' file : ' || v_file_name);
19
20 i := i+1;
21 end loop;
22 end;
23 /
row no: 1 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 2 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 3 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 4 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 5 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 6 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 7 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 8 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 9 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 10 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 11 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 12 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 13 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 14 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 15 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 16 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 17 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 18 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 19 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 20 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 21 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 22 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 23 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 24 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 25 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 26 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 27 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 28 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 29 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 30 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 31 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 32 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 33 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 34 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 35 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 36 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 37 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 38 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 39 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 40 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 41 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 42 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 43 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 44 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 45 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 46 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 47 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 48 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 49 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 50 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 51 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 52 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 53 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 54 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 55 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 56 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 57 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 58 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 59 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 60 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 61 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 62 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 63 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 64 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 65 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 66 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 67 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 68 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 69 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 70 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 71 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 72 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 73 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 74 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 75 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 76 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 77 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 78 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 79 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 80 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 81 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 82 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 83 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 84 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 85 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 86 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 87 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 88 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 89 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 90 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 91 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 92 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 93 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 94 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 95 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 96 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 97 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 98 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 99 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
row no: 100 file : /ora01/oradata/ORCL/bubbatbs_02.dbf
PL/SQL procedure successfully completed.
SQL> /*
SQL> =========================================== */
SQL> --
SQL> col file_name for a35
SQL> select e.extent_id,
2 e.file_id,
3 f.file_name
4 from dba_extents e,
5 dba_data_files f
6 where e.owner = 'BUBBA'
7 and e.segment_name = 'ROWID_TEST'
8 and e.file_id = f.file_id
9 order by e.extent_id
10 ;
EXTENT_ID FILE_ID FILE_NAME
---------- ---------- -----------------------------------
0 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
1 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
2 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
3 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
4 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
5 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
6 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
7 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
8 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
9 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
10 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
11 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
12 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
13 9 /ora01/oradata/ORCL/bubbatbs_02.dbf
14 rows selected.
Fig. 5
Conclusions
So, in this case, as in most things dealing with databases in general and Oracle in particular, the answer is “It depends”. At least with the variables I was controlling for, it depends on if the tablespace is defined as UNIFORM extents, or AUTOALLCOATE extents. I have not explored the behavior with dictionary managed tablespaces simply because they were rendered obsolete by the use of locally managed tablespaces. Anyone still using dictionary managed tablespaces needs to be more concerned about migrating to locally managed than how oracle manages those obsolete structures.
