1

Let's say I have a single datafile, DF1. After DF1 becomes 50% full, I add a new datafile DF2 of equal size. Both datafiles have autoextend set to ON. When file DF1 becomes full (and therefore, DF2 will be around 50% full), will it autoextend DF1 despite having free space in DF2? Or will it instead leave DF1 as is (100% full) and start allocating extents from DF2 only?

Here's a visualization

When I add new datafile:
DF1: xxxxxxx00000000
DF2: 000000000000000

After DF1 becomes full:
DF1: xxxxxxxxxxxxxxx
DF2: xxxxxxx00000000


Will it start to do this?

DF1: xxxxxxxxxxxxxxxxxxx
DF2: xxxxxxxxxxx0000

Or will it start to do this?

DF1: xxxxxxxxxxxxxxx
DF2: xxxxxxxxxxxxxxx
1

Extents will spread evenly:

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

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> create tablespace test datafile size 10M autoextend on next 10M maxsize 100M;

Tablespace created.

SQL> create table t1 (id number) tablespace test;

Table created.

SQL> alter table t1 allocate extent (size 50M);

Table altered.

SQL> select * from dba_tablespace_usage_metrics where tablespace_name = 'TEST';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
TEST                                 6536           12800      51.0625

SQL> alter tablespace test add datafile size 10M autoextend on next 10M maxsize 100M;

Tablespace altered.

SQL> select file_id, sum(blocks) from dba_extents where tablespace_name = 'TEST' group by file_id;

   FILE_ID SUM(BLOCKS)
---------- -----------
         7        6408

SQL> alter table t1 allocate extent (size 30M);

Table altered.

SQL> select file_id, sum(blocks) from dba_extents where tablespace_name = 'TEST' group by file_id;

   FILE_ID SUM(BLOCKS)
---------- -----------
         8        1536
         7        8712

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.