ALTER CLUSTER

Syntax

physical_attributes_clause::=

storage_clause: See "storage_clause".

allocate_extent_clause::=

deallocate_unused_clause::=

parallel_clause::=

Purpose

To redefine storage and parallelism characteristics of a cluster.

For information on creating a cluster, see "CREATE CLUSTER".

To remove tables from a cluster, see "DROP CLUSTER" and "DROP TABLE".

Note: 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.

Prerequisites

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

Keywords and Parameters

schema	is the schema containing the cluster. If you omit schema, Oracle assumes the cluster is in your own schema.
cluster	is the name of the cluster to be altered.
physical_attributes_clause	changes the values of the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters of the cluster. For a description of these parameters, see "CREATE CLUSTER".
	storage_clause	changes the storage characteristics for the cluster. See the "storage_clause". Restriction: You cannot change the values of the storage parameters INITIAL and MINEXTENTS for a cluster.
SIZE integer	determines how many cluster keys will be stored in data blocks allocated to the cluster. For a description of the SIZE parameter, see "CREATE CLUSTER". Restriction: You can change the SIZE parameter only for an indexed cluster, not for a hash cluster.
allocate_extent_clause	explicitly allocates a new extent for the cluster. Restriction: You can allocate a new extent only for an indexed cluster, not for a hash cluster.
	SIZE	specifies the size of the extent in bytes. Use K or M to specify the extent size in kilobytes or megabytes. When you explicitly allocate an extent with this clause, Oracle does not evaluate the cluster's storage parameters 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 to use a default value.
	DATAFILE	specifies one of the datafiles in the cluster's tablespace to contain the new extent. If you omit this parameter, Oracle chooses the datafile.
	INSTANCE	makes the new extent available to the specified instance. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit INSTANCE, the extent is available to all instances. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
deallocate_unused_clause	explicitly deallocates unused space at the end of the cluster and makes the freed space available for other segments. Only unused space above the high water mark can be freed.
	KEEP	specifies the number of bytes above the high water mark that the cluster will have after deallocation. If the number of remaining extents is less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent. If you omit KEEP, all unused space is freed.
	For a more complete description of this clause, see "ALTER TABLE".
parallel_clause	changes the default degree of parallelism for queries and DML on the cluster. See Also: The Notes to the parallel_clause of "CREATE TABLE".
	NOPARALLEL	specifies serial execution. This is the default.
	PARALLEL	causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
	PARALLEL integer	specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
	Restriction: If the tables in cluster contain any columns of LOB or user-defined object type, this statement as well as subsequent INSERT, UPDATE, or DELETE operations on cluster are executed serially without notification.

Examples

The following statement alters the CUSTOMER cluster in the schema SCOTT:

ALTER CLUSTER scott.customer 
    SIZE 512 
    STORAGE (MAXEXTENTS 25); 

Oracle allocates 512 bytes for each cluster key value. Assuming a data block size of 2 kilobytes, future data blocks within this cluster contain 4 cluster keys per data block, or 2 kilobytes divided by 512 bytes. The cluster can have a maximum of 25 extents.

The following statement deallocates unused space from the CUSTOMER cluster, keeping 30 kilobytes of unused space for future use:

ALTER CLUSTER scott.customer 
    DEALLOCATE UNUSED KEEP 30 K;