Oracle7 Server SQL Reference

ALTER CLUSTER

Purpose

To redefine storage and parallelism characteristics for a cluster.

Prerequisites

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

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the cluster's creation label or you must satisfy one of these criteria:

If the cluster's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the cluster's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the cluster's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

is the schema containing the cluster. If you omit schema, Oracle7 assumes the cluster is in your own schema.

cluster

is the name of the cluster to be altered.

SIZE

determines how many cluster keys will be stored in data blocks allocated to the cluster. You can only change the SIZE parameter for an indexed cluster, not for a hash cluster. For a description of the SIZE parameter, see the CREATE CLUSTER command .

PCTUSED PCTFREE INITRANS MAXTRANS

changes the values of these parameters for the cluster. See the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters of the CREATE CLUSTER command .

STORAGE

changes the storage characteristics for the cluster. See the STORAGE clause .

ALLOCATE EXTENT

explicitly allocates a new extent for the cluster.

SIZE specifies the size of the extent in bytes. You can use K or M to specify the extent size in kilobytes or megabytes. If you omit this parameter, Oracle7 determines the size based on the values of the cluster's STORAGE parameters.

DATAFILE specifies one of the datafiles in the cluster's tablespace to contain the new extent. If you omit this parameter, Oracle7 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 this parameter, the extent is available to all instances. Only use this parameter if you are using Oracle7 with the Parallel Server option in parallel mode.

Explicitly allocating an extent with this clause does not cause Oracle7 to evaluate the cluster's storage parameters and determine a new size for the next extent to be allocated. You can only allocate a new extent for an indexed cluster, not a hash cluster.

DEALLOCATE UNUSED

explicitly deallocates unused space at the end of the cluster and make the freed space available for other segments. Only unused space above the high-water mark can be freed. If KEEP is omitted, all unused space is freed. For more information, see the deallocate_clause .

KEEP specifies the number of bytes above the high-water mark that the cluster will have after deallocation. If the number of remaining extents are 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.

PARALLEL

specifies the degree of parallelism for creating the cluster and the default degree of parallelism for queries on the cluster once created. For more information, see the parallel_clause .

Usage Notes

You can perform these tasks with the ALTER CLUSTER command:

change the MAXTRANS parameter value for data blocks in the cluster

change the SIZE, PCTUSED, PCTFREE, and INITRANS parameter values for future data blocks in the cluster

change future storage characteristics with the STORAGE characteristics NEXT, PCTINCREASE, and MAXEXTENTS

explicitly allocate an extent

explicitly deallocate space from unused extents

You cannot perform these tasks with the ALTER CLUSTER command:

change the number or the name of columns in the cluster key

change the values of the STORAGE parameters INITIAL and MINEXTENTS

change the tablespace in which the cluster is stored

remove tables from a cluster (see the DROP CLUSTER command and DROP TABLE command )

Example I

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

ALTER CLUSTER scott.customer

	SIZE 512

	STORAGE (MAXEXTENTS 25)

Oracle7 now 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.

Example II

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