Problem
You’re installing an application that has thousands of tables and indexes. Each table and index are configured to initially allocate an initial extent of 10 MB. When deploying the installation DDL to your production environment, you want install the database objects as fast as possible. You realize it will take some time to deploy the DDL if each object allocates 10 MB of disk space as it is created. You wonder if you can somehow instruct Oracle to defer the initial extent allocation for each object until data is actually inserted into a table.
Solution
The only way to defer the initial segment generation is to use the Enterprise Edition of Oracle Database 11g R2 or higher. With the Enterprise Edition of Oracle, by default the physical allocation of the extent for a table (and associated indexes) is deferred until a record is first inserted into the table. A small example will help illustrate this concept. First a table is created:
create table emp(
emp_id number
,first_name varchar2(30)
,last_name varchar2(30));
Now query USER_SEGMENTS
and USER_EXTENTS
to verify that no physical space has been allocated:
SQL> select count(*) from user_segments where segment_name='EMP';
COUNT(*)
----------
0
SQL> select count(*) from user_extents where segment_name='EMP';
COUNT(*)
----------
0
Next a record is inserted, and the prior queries are run again:
SQL> insert into emp values(1,'John','Smith');
1 row created.
SQL> select count(*) from user_segments where segment_name='EMP';
COUNT(*)
----------
1
SQL> select count(*) from user_extents where segment_name='EMP';
COUNT(*)
----------
1