Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER CLUSTER to ALTER SEQUENCE, 2 of 18
Use the ALTER
CLUSTER
statement to redefine storage and parallelism characteristics of a cluster.
The cluster must be in your own schema or you must have ALTER
ANY
CLUSTER
system privilege.
alter_cluster::=
alter_cluster
storage_clause: See storage_clause
allocate_extent_clause
deallocate_unused_clause
parallel_clause
Specify the schema containing the cluster. If you omit schema
, Oracle 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
, INITRANS
, and MAXTRANS
parameters of the cluster.
Use the STORAGE
clause to change the storage characteristics of the cluster.
Restriction: You cannot change the values of the storage parameters INITIAL
and MINEXTENTS
for a cluster.
Use the SIZE
clause to specify the number of cluster keys that will be stored in data blocks allocated to the cluster.
Restriction: You can change the SIZE
parameter only for an indexed cluster, not for a hash cluster.
Specify the ALLOCATE
EXTENT
clause to explicitly allocate a new extent for the cluster.
Restriction: You can allocate a new extent only for an indexed cluster, not for a hash cluster.
Use the SIZE
parameter to specify 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.
Use the DATAFILE
parameter to specify one of the datafiles in the cluster's tablespace to contain the new extent. If you omit this parameter, Oracle chooses the datafile.
Use the INSTANCE
parameter to make 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.
Specify the DEALLOCATE
UNUSED
clause to explicitly deallocate 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.
Use the KEEP
parameter to specify 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.
Specify CACHE
if you want the blocks retrieved for this cluster to be placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This clause is useful for small lookup tables.
Specify NOCACHE
if you want the blocks retrieved for this cluster to be placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.
Specify the parallel_clause
to change the default degree of parallelism for queries and DML
on the cluster.
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.
Specify NOPARALLEL
for serial execution. This is the default.
Specify PARALLEL
if you want 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.
Specification of integer
indicates 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 servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer
.
The following examples modify the clusters that were created in the "Examples" section of CREATE
CLUSTER
on .
The following statement alters the personnel
cluster:
ALTER CLUSTER personnel SIZE 1024 CACHE;
Oracle allocates 1024 bytes for each cluster key value and turns on 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.
The following statement deallocates unused space from the language
cluster, keeping 30 kilobytes of unused space for future use:
ALTER CLUSTER language DEALLOCATE UNUSED KEEP 30 K;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|