[Laskey99] 13.2. SQL Command Syntax

13.2.
This section provides a detailedreference to each of the SQL commands likely to be used by the DBA.Each command is listed in one or more of its forms, with the exactsyntax of the command and its associated parameters, a shortexplanation of the purpose of the command, an example, and usagenotes where applicable.

Many statements allow you to specify a schema name. For example, if you specify:
CREATE TABLE schema. table_name
the table is created in the specified schema. If you simply specify:
CREATE TABLE table_name
the table is created in the schema of the user executing the command.
ALTER CLUSTER
ALTER CLUSTER
[ schema.] cluster_name
[PCTFREE integer]
[PCTUSED integer]
[SIZE integer[K | M]]
[INITRANS integer]
[MAXTRANS integer]
[STORAGE (
[NEXT integer[K | M] ]
[MAXEXTENTS { integer | UNLIMITED} ]
[PCTINCREASE integer]
[FREELISTS integer]
[FREELIST GROUPS integer]
) ]
[ALLOCATE EXTENT
(SIZE integer[K | M] [DATAFILE
' filename'] [INSTANCE
integer])]
[DEALLOCATE UNUSED [KEEP integer[K | M]]
[PARALLEL ( {DEGREE { integer | DEFAULT} |
INSTANCES { integer | DEFAULT} } ) ]
[NOPARALLEL]
Redefines future storage allocations or allocates an extent for acluster (cluster_name).
Keywords
PCTFREE
Changes the percentage of space that will be kept free for futureupdates to the rows contained in this cluster. The value may be inthe range- 99 and defaults to 10.
PCTUSED
Changes the minimum percentage of used space that Oracle willmaintain in each block. The value may be in the range- 99 and defaults to 40.
SIZE
Determines how many cluster keys will be stored in each data block ofthis cluster. The value should be a divisor of the Oracle blocksize,and will be rounded up to the next larger divisor if necessary.
INITRANS
Changes the number of transaction entries allocated to each block inthe cluster. The value may be in the range 1-255 and should notnormally be changed from the default of 2.
MAXTRANS
Changes the maximum number of concurrent transactions that can updateblocks of the cluster. The value may be in the range 1-255 and shouldnot normally be changed from the default, which is a function of theOracle blocksize.
STORAGE
Specifies the physical characteristics of the cluster as follows:
NEXT
Specifies the sizeof the next extent in bytes, kilobytes, or megabytes. If the value isnot a multiple of the database blocksize, it will be rounded up to amultiple of the database blocksize.
MAXEXTENTS
Specifies the maximum number ofextents that may be allocated for this cluster. The default will varyaccording to the database blocksize. Specify UNLIMITED for unlimitedexpansion.
PCTINCREASE
Specifies the percentage by which each extent will grow over theprevious extent. The default is 50, which means that each extent willbe one- and-one-half times larger than the previous extent.
FREELISTS
Specifies the number of free lists contained in each freelist groupin this cluster. The default is 1 and the maximum depends on thedatabase blocksize.
FREELIST GROUPS
Specifies the number of groups of free lists for this cluster. Thedefault is 1. This parameter should be used only with the ParallelServer option running in parallel mode.
ALLOCATE EXTENT
Forces the immediate allocation of the next extent.
SIZE
Specifies the size of the new extentin bytes, kilobytes, or megabytes.
DATAFILE
Specifies the name of the operatingsystem datafile ( filename) in the tablespace inwhich this cluster resides to hold the new extent. If this value isomitted, Oracle will select a datafile.
INSTANCE
Makes the new extentavailable to the specified instance, which is identified by theinitialization parameter INSTANCE_NUMBER. This parameter can only beused when running in parallel mode.
DEALLOCATE UNUSED
Releases storage above the highwater mark.
KEEP
Specifies the amount of storage abovethe highwater mark to keep.
PARALLEL
Specifies the level of parallelism tobe supported, based on the following parameters:
DEGREE
Specifies the degree of parallelism. An integer value specifies howmany slave processes can be used. Specify DEFAULT to use the defaultvalue specified for the tablespace.
INSTANCES
Specifies the number of instances that can be used to execute slaveprocesses. Specify DEFAULT to use the default value specified for thetablespace.
NOPARALLEL
Specifies that no paralleloperations are to be performed.
Note
The cluster must be in your schema or you must have the ALTER ANYCLUSTER privilege to issue this command.
Example
The following example alters an existing cluster to allocate 512bytes per block to cluster keys and allow a maximum of 20 extents forthe cluster:
ALTER CLUSTER demo.employee
SIZE 512
STORAGE (MAXEXTENTS 20);

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint
Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next
Related Content

SQL Statement Syntax
From: Oracle SQL: The Essential Reference
SQL Statement Syntax
From: Oracle SQL: The Essential Reference
ALTER SESSION
From: Oracle in a Nutshell
Storage_Clause
From: Oracle in a Nutshell
CREATE/ALTER/DROP TABLE (Relational Syntax)
From: Oracle in a Nutshell
Storage_Clause
From: Oracle SQL: The Essential Reference
CREATE CONTROLFILE
From: Oracle in a Nutshell
ANALYZE
From: Oracle in a Nutshell
Basic Initialization (init.ora) Parameters
From: Oracle® DBA Automation Quick Reference
CREATE/ALTER DATABASE
From: Oracle in a Nutshell