Oracle7 Server Administrator's Guide

Altering Clusters

You can alter an existing cluster to change the following settings:

data block space usage parameters (PCTFREE, PCTUSED)

the average cluster key size (SIZE)

transaction entry settings (INITRANS, MAXTRANS)

storage parameters (NEXT, PCTINCREASE)

To alter a cluster, your schema must contain the cluster or you must have the ALTER ANY CLUSTER system privilege.

When you alter data block space usage parameters (PCTFREE and PCTUSED) or the cluster size parameter (SIZE) of a cluster, the new settings apply to all data blocks used by the cluster, including blocks already allocated and blocks subsequently allocated for the cluster. Blocks already allocated for the table are reorganized when necessary (not immediately).

When you alter the transaction entry settings (INITRANS, MAXTRANS) of a cluster, a new setting for INITRANS applies only to data blocks subsequently allocated for the cluster, while a new setting for MAXTRANS applies to all blocks (already and subsequently allocated blocks) of a cluster.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the cluster.

To alter a cluster, use the SQL command ALTER CLUSTER. The following statement alters the EMP_DEPT cluster:

ALTER CLUSTER emp_dept

   PCTFREE 30

   PCTUSED 60;

Altering Cluster Tables and Cluster Indexes

You can alter clustered tables using the SQL command ALTER TABLE. However, any data block space parameters, transaction entry parameters, or storage parameters you set in an ALTER TABLE statement for a clustered table generate an error message (ORA-01771, "illegal option for a clustered table"). Oracle uses the parameters of the cluster for all clustered tables. Therefore, you can use the ALTER TABLE command only to add or modify columns, or add, drop, enable, or disable integrity constraints or triggers for a clustered table.

Note: When estimating the size of cluster indexes, remember that the index is on each cluster key, not the actual rows; therefore, each key will only appear once in the index.

Manually Allocating Storage for a Cluster

Oracle dynamically allocates additional extents for the data segment of a cluster as required. In some circumstances, however, you might want to allocate an additional extent for a cluster explicitly. For example, when using the Oracle Parallel Server, you can allocate an extent of a cluster explicitly for a specific instance.

You allocate a new extent for a cluster using the SQL command ALTER CLUSTER with the ALLOCATE EXTENT option.

See Also: For information about altering tables, see "Altering Tables" .

You alter cluster indexes exactly as you do other indexes. For more information, see "Altering an Index" .

For more information about the CLUSTER parameter in the ALTER CLUSTER command, see the Oracle7 Parallel Server Concepts & Administration guide.