Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF



Use the allocate_extent_clause clause to explicitly allocate a new extent for a database object.

Explicitly allocating an extent with this clause does not change the values of the NEXT and PCTINCREASE storage parameters, so does not affect the size of the next extent to be allocated implicitly by Oracle.

See Also:

storage_clause for information about the NEXT and PCTINCREASE storage parameters

You can allocate an extent in the following SQL statements:



Text description of clauses17.gif follows
Text description of allocate_extent_clause


This section describes the parameters of the allocate_extent_clause. For additional information, refer to the SQL statement in which you set or reset these parameters for a particular database object.


You cannot specify the allocate_extent_clause and the deallocate_unused_clause in the same statement.


Specify the size of the extent in bytes. Use K or M to specify the extent size in kilobytes or megabytes.

For a table, index, materialized view, or materialized view log, if you omit SIZE, then Oracle determines the size based on the values of the storage parameters of the object. However, for a cluster, Oracle does not evaluate the cluster's storage parameters, so you must specify SIZE if you do not want Oracle to use a default value.

DATAFILE 'filename'

Specify one of the datafiles in the tablespace of the table, cluster, index, materialized view, or materialized view log to contain the new extent. If you omit DATAFILE, then Oracle chooses the datafile.

INSTANCE integer

Use this parameter only if you are using Oracle with Real Application Clusters.

Specifying INSTANCE integer makes the new extent available to the freelist group associated with the specified instance. If the instance number exceeds the maximum number of freelist groups, then Oracle divides the specified number by the maximum number and uses the remainder to identify the freelist group to be used. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER.

If you omit this parameter, then the space is allocated to the table, cluster, index, materialized view, or materialized view log but is not drawn from any particular freelist group. Instead, Oracle uses the master freelist and allocates space as needed.


If you are using automatic segment-space management, then the INSTANCE parameter of the allocate_extent_clause may not reserve the newly allocated space for the specified instance, because automatic segment-space management does not maintain rigid affinity between extents and instances.

See Also:

Oracle9i Real Application Clusters Administration for more information on setting the INSTANCE parameter of allocate_extent_clause