Allocate Extent

This post, which was tested on a version 9 database, shows how you can allocate extents to a table or index manually as opposed to allowing Oracle to create them automatically. Start by creating a table and looking at the size of its first extent:

 
SQL> create table andrews_table
  2  (col1 varchar2(10))
  3  tablespace user_data
  4  /

Table created.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480

SQL> 

Now do the same with an index:

SQL> create index andrews_index
  2  on andrews_table (col1)
  3  tablespace user_data
  4  /

Index created.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_INDEX'
  3  /

EXTENT_ID      BYTES
---------- ----------
        0      20480

SQL>

When you add an extent manually, you can allow Oracle to decide its size: 

SQL> alter table andrews_table allocate extent
  2  /

Table altered.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  order by 1
  4  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480
         1      20480

SQL>

... or you can specify it yourself like this: 

SQL> alter index andrews_index allocate extent
  2  (size 40k)
  3  /

Index altered.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_INDEX'
  3  order by 1
  4  /

 EXTENT_ID      BYTES
---------- ----------
         0      20480
         1      40960

SQL>

You can even tell Oracle which datafile to put the extent in. However, in this example it makes no difference as the tablespace only has 1 datafile:

SQL> alter table andrews_table allocate extent
  2  (size 60k
  3  datafile '/datafiles/user_data.dbf')
  4  /

Table altered.

SQL> select extent_id, file_id, bytes from dba_extents
  2  where segment_name = 'ANDREWS_TABLE'
  3  order by 1
  4  /

 EXTENT_ID    FILE_ID      BYTES
---------- ---------- ----------
         0         22      20480
         1         22      20480
         2         22      61440

SQL>

But, if you specify a datafile which does not exist or, as in this case, belongs to another tablespace, you get an ORA-03283:

SQL> alter index andrews_index allocate extent
  2  (datafile '/datafiles/system.dbf')
  3  /
alter index andrews_index allocate extent
*
ERROR at line 1:
ORA-03283: specified datafile /datafiles/system.dbf does not exist

SQL>