oracle dba_temp_files,Oracle Data & Temp Files Management

CREATE TABLESPACE bowie_data

DATAFILE 'c:\bowie\bowie_data01.dbf' size 10m,

'c:\bowie\bowie_data02.dbf' size 10m,

'c:\bowie\bowie_data03.dbf' size 10m

uniform size 64;

col segment_name format a30

SELECT file_id, file_name

FROM dba_data_files

WHERE tablespace_name = 'BOWIE_DATA';

CREATE TABLE one (x NUMBER) TABLESPACE bowie_data;

CREATE TABLE two (x NUMBER) TABLESPACE bowie_data;

CREATE TABLE three (x NUMBER) TABLESPACE bowie_data;

CREATE TABLE four (x NUMBER) TABLESPACE bowie_data;Now we've create 4 tables in this tablespace. Let's see which data file they were placed

in ...SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA';Note that *all* tables have their first extent created in the *first* data file defined to

the tablespace.

Now lets grow these tables and see what happens next.ALTER TABLE one ALLOCATE EXTENT;

ALTER TABLE two ALLOCATE EXTENT;

ALTER TABLE three ALLOCATE EXTENT;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA'

ORDER BY segment_name;... and the second extent of each table has been created in the second data file of the

tablespace.

If a particular table were to keep growing ...ALTER TABLE four ALLOCATE EXTENT;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA'

ORDER BY segment_name;You can see how the extents get allocated to the data files in a round robin fashion. But

the first extent is allocate to the first data file (providing it has sufficent space) ...CREATE TABLE five (x NUMBER) TABLESPACE bowie_data;

SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA'

ORDER BY segment_name;Let's add a new data file. What happens now ...ALTER TABLESPACE bowie_data

ADD DATAFILE 'c:\bowie\bowie_data04.dbf' SIZE 10M;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA'

ORDER BY segment_name;A new extent is added to table four. And uses the new datafile.ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA'

ORDER BY segment_name;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id

FROM dba_extents

WHERE tablespace_name = 'BOWIE_DATA'

ORDER BY segment_name;... and now the new file is used. The files are still used in a round robin fashion with

the new file slipping in.

Note how file 16 is the *most* used file and file 19 is the least. If I were to allocate

several new tables that were only 1 or 2 extents in size, see how file 16 would be the one

to be most "filled".