Creating and Altering Clusters

View All Scripts Login to Run Script

Script Name Creating and Altering Clusters
Description Create a cluster, modify the cluster key size, and deallocate unused space that is allocated to the cluster.
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1

This statement creates a cluster with a default key size, 600.

Create cluster

CREATE CLUSTER EMP_DEPT (DEPTNO NUMBER(3))   
   SIZE 600   
   TABLESPACE LIVESQL_USERS   
   STORAGE (INITIAL 200K   
      NEXT 300K   
      MINEXTENTS 2   
      PCTINCREASE 33)

Statement processed.

Statement 2

This statement queries USER_CLUSTERS to display the cluster metadata. Observe the key size value.

Display the cluster metadata information

SELECT CLUSTER_NAME, TABLESPACE_NAME, KEY_SIZE, CLUSTER_TYPE, AVG_BLOCKS_PER_KEY, MIN_EXTENTS, MAX_EXTENTS FROM USER_CLUSTERS


CLUSTER_NAME TABLESPACE_NAME KEY_SIZE CLUSTER_TYPE AVG_BLOCKS_PER_KEY MIN_EXTENTS MAX_EXTENTS
EMP_DEPT LIVESQL_USERS 600 INDEX  - 1 2147483645

Statement 3
This statement modifies the cluster key size by using the ALTER statement.
Alter cluster key size
```
ALTER CLUSTER EMP_DEPT SIZE 1024
```
```
Statement processed.
```

CLUSTER_NAME	TABLESPACE_NAME	KEY_SIZE	CLUSTER_TYPE	AVG_BLOCKS_PER_KEY	MIN_EXTENTS	MAX_EXTENTS
EMP_DEPT	LIVESQL_USERS	600	INDEX	-	1	2147483645

Statement 4

This statement displays the displays the metadata of the modified cluster.

Display the cluster metadata information after altering

SELECT CLUSTER_NAME, TABLESPACE_NAME, KEY_SIZE, CLUSTER_TYPE, AVG_BLOCKS_PER_KEY, MIN_EXTENTS, MAX_EXTENTS FROM USER_CLUSTERS


CLUSTER_NAME TABLESPACE_NAME KEY_SIZE CLUSTER_TYPE AVG_BLOCKS_PER_KEY MIN_EXTENTS MAX_EXTENTS
EMP_DEPT LIVESQL_USERS 1024 INDEX  - 1 2147483645

Statement 5
This statement deallocates the unused space from the cluster, keeping 30 kilobytes of unused space for future use.
Deallocate unused space allocated to the cluster
```
ALTER CLUSTER EMP_DEPT DEALLOCATE UNUSED KEEP 30 K
```
```
Statement processed.
```

CLUSTER_NAME	TABLESPACE_NAME	KEY_SIZE	CLUSTER_TYPE	AVG_BLOCKS_PER_KEY	MIN_EXTENTS	MAX_EXTENTS
EMP_DEPT	LIVESQL_USERS	1024	INDEX	-	1	2147483645

Statement 6

SELECT CLUSTER_NAME, TABLESPACE_NAME, KEY_SIZE, CLUSTER_TYPE, AVG_BLOCKS_PER_KEY, MIN_EXTENTS, MAX_EXTENTS FROM USER_CLUSTERS


CLUSTER_NAME TABLESPACE_NAME KEY_SIZE CLUSTER_TYPE AVG_BLOCKS_PER_KEY MIN_EXTENTS MAX_EXTENTS
EMP_DEPT LIVESQL_USERS 1024 INDEX  - 1 2147483645

CLUSTER_NAME	TABLESPACE_NAME	KEY_SIZE	CLUSTER_TYPE	AVG_BLOCKS_PER_KEY	MIN_EXTENTS	MAX_EXTENTS
EMP_DEPT	LIVESQL_USERS	1024	INDEX	-	1	2147483645

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database