Avoiding Extent Allocation Delays When Creating Tables in Oracle 12C

Avoiding Extent Allocation Delays When Creating Tables in Oracle 12C

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

 

Вас заинтересует / Intresting for you:

Stopping the Oracle Database 1...
Stopping the Oracle Database 1... 390 views Андрей Волков Sat, 29 Feb 2020, 10:19:28
How to connect to Oracle Datab...
How to connect to Oracle Datab... 606 views Андрей Волков Sat, 29 Feb 2020, 10:19:58
Oracle Database and Instance d...
Oracle Database and Instance d... 866 views Masha Thu, 21 Jun 2018, 18:23:39
Oracle Database 12C Backup: un...
Oracle Database 12C Backup: un... 593 views Андрей Волков Sat, 29 Feb 2020, 10:15:04