A cluster is a database object and like other database objects, you can delete is from the database.
To drop a cluster, you use the syntax illustrated in the following display:
Dropping Clustered Tables
To drop a cluster, your schema must contain the cluster or you must have the DROP ANY CLUSTER system privilege. You do not have to have additional privileges to drop a cluster that contains tables, even if the clustered tables are not owned by the owner of the cluster.
Clustered tables can be dropped individually without affecting
the table's cluster,
other clustered tables, or
the cluster index.
A clustered table is dropped just as a non-clustered table is dropped using the DROP TABLE statement.
Note: When you drop a single table from a cluster, Oracle deletes each row of the table individually.
To maximize efficiency when you intend to drop an entire cluster, drop the cluster including all tables by using the DROP CLUSTER statement with
the INCLUDING TABLES option. Drop an individual table from a cluster (using the DROP TABLE statement) only if you want the rest of the cluster to remain.
Drop Oracle Cluster
When you drop a cluster, the cluster index, if one exists, is also dropped. You can drop a table from a cluster that contains multiple tables by using the DROP TABLE command. However, the result of this action will be that Oracle individually deletes each row of the table. There is no way to uncluster a table, since the cluster actually controls the physical placement of the table on the disk. If you want to change a clustered table to an unclustered table, you must first unload the data from the table, drop the cluster, create the table again without a CLUSTER clause, and reload the data back into the table.
Oracle Cluster Example
If you wanted to drop the existing lot_cluster cluster, and the cluster contained tables, you would use the following SQL command:
DROP CLUSTER lot_cluster INCLUDING TABLES;
The next lesson concludes the module on Oracle table clustering which will briefly review the topics covered in this module.
You also can take a quiz to help identify topics that you might want to review in more detail.