Oracle Tips by Burleson
Dropping a Cluster and
As with the creation of clusters, dropping a
cluster is a multistep function. The first step is to drop the
tables in the cluster using the DROP TABLE command. Next, the
cluster is dropped with the DROP CLUSTER command. An INCLUDING
TABLES clause allows the DBA to drop both the cluster and tables at
the same time. The format of the command can be found on the
documentation website at technet.oracle.com.
Rather than dropping a cluster’s tables
completely, it may be desirable to decluster them and then just drop
the cluster and cluster index with the DROP CLUSTER command. The
procedure to decluster a table follows:
1. Create a new table that is a mirror of
the existing clustered table—except , of course, it isn’t clustered.
CREATE TABLE new table
AS SELECT * FROM cluster table
Remember that “new table” is a different
name from “cluster table.”
2. Drop the clustered table.
DROP TABLE cluster table
3. Rename the replacement table.
RENAME new table TO cluster table
4. Reassign all grants; create required
indexes and constraints.
In the sample SQL scripts zip file on the
Wiley website there is a dynamic SQL script that will create a grant
script for a specified database object. The script must, of course,
be run before the object is dropped.
5. Drop the cluster.
See Code Depot