Use the ALTER CLUSTER statement to redefine storage and parallelism characteristics of a cluster.


You cannot use this statement to change the number or the name of columns in the cluster key, and you cannot change the tablespace in which the cluster is stored.

See Also:

CREATE CLUSTER for information on creating a cluster, DROP CLUSTER and DROP TABLE for information on removing tables from a cluster, and CREATE TABLE ... physical_properties for information on adding a table to a cluster


The cluster must be in your own schema or you must have the ALTER ANY CLUSTER system privilege.



Specify the schema containing the cluster. If you omit schema, then Oracle Database assumes the cluster is in your own schema.


Specify the name of the cluster to be altered.


Use this clause to change the values of the PCTUSED, PCTFREE, and INITRANS parameters of the cluster.

Use the STORAGE clause to change the storage characteristics of the cluster.

See Also:

Restriction on Physical Attributes You cannot change the values of the storage parameters INITIAL and MINEXTENTS for a cluster.

SIZE integer

Use the SIZE clause to specify the number of cluster keys that will be stored in data blocks allocated to the cluster.

Restriction on SIZE You can change the SIZE parameter only for an indexed cluster, not for a hash cluster.

See Also:

CREATE CLUSTER for a description of the SIZE parameter and "Modifying a Cluster: Example"


Specify MODIFY PARTITION partition allocate_extent_clause to explicitly allocate a new extent for a cluster partition. This operation is valid only for range-partitioned hash clusters and is available starting with Oracle Database 12c Release 1 ( For partition, specify the cluster partition name.


Specify allocate_extent_clause to explicitly allocate a new extent for a cluster. This operation is valid only for indexed clusters and nonpartitioned hash clusters.

When you explicitly allocate an extent with the allocate_extent_clause, Oracle Database does not evaluate the storage parameters of the cluster and determine a new size for the next extent to be allocated (as it does when you create a table). Therefore, specify SIZE if you do not want Oracle Database to use a default value.

See Also:

allocate_extent_clause for a full description of this clause


Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the cluster and make the freed space available for other segments.

See Also:

deallocate_unused_clause for a full description of this clause and "Deallocating Unused Space: Example"


This clause has the same behavior in CREATE CLUSTER and ALTER CLUSTER statements.

See Also:

"CACHE | NOCACHE" for information on this clause.


Specify the parallel_clause to change the default degree of parallelism for queries on the cluster.

See Also:

parallel_clause in the documentation on CREATE TABLE for complete information on this clause


The following examples modify the clusters that were created in the CREATE CLUSTER "Examples".

Modifying a Cluster: Example The next statement alters the personnel cluster:

   SIZE 1024 CACHE;

Oracle Database allocates 1024 bytes for each cluster key value and enables the cache attribute. Assuming a data block size of 2 kilobytes, future data blocks within this cluster contain 2 cluster keys in each data block, or 2 kilobytes divided by 1024 bytes.

Deallocating Unused Space: Example The following statement deallocates unused space from the language cluster, keeping 30 kilobytes of unused space for future use: