ORA-03292: Table to be truncated is part of a cluster

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Attempting to perform an operation like:

TRUNCATE TABLE clustered_table;

you've got the following error:

ORA-03292: Table to be truncated is part of a cluster

the data of the tables belonging to a cluster are all stored together in the same block, in other words a block (or more than one if necessary...) contains the data of the columns of multiple tables.
Imagine the rows in a join, typically you want to display one or more columns from table A side by side with columns taken from B. In a cluster you would find those values stored in a similar fashion, first the common key value, then the columns of table A, then the columns of table B and so on.

See a graphical representation of clusters in the Concepts book.

Table truncation is an expedite way of deleting the rows in a table by dropping or erasing the allocated blocks, depending on the optional DROP STORAGE or REUSE STORAGE clause you choose. In other terms, TRUNCATE works at the block level, not at the row level like a DELETE statement.
Therefore you should now understand why a cluster block containing data from multiple tables, cannot be drop or erased in the same easy way, unless you truncate the entire cluster.
Honestly, i thought that Oracle would allow an exception to this rule with single table hash clusters, but it doesn't, on the contrary, given a hash cluster, it will even complain if you try a TRUNCATE CLUSTER (see ORA-03293)!

So, it turns out that you can only truncate an entire cluster if the cluster type is indexed.

In conclusion, in order to erase the data of a single table, perform a DELETE statement, otherwise, for erasing all tables participating in a hash cluster, DROP and re-CREATE the cluster, finally, for erasing all tables in indexed clusters you can use TRUNCATE CLUSTER.

Clusters are meant to store data that doesn't change frequently and, given their nature, it is often required the development of specific loading programs, so dropping and recreating a cluster makes sense compared to traditional tables.

See message translations for ORA-03292 and search Oracle Documentation

ORA-00951: cluster not empty

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A cluster can be dropped only if the participating tables have been dropped first, otherwise you'll get:

ORA-00951: cluster not empty

Alternatively, if you are absolutely sure, you can add the INCLUDING TABLES clause:

drop CLUSTER dummy_cluster INCLUDING TABLES
/

If there are any tables whose constraints point to the cluster table and are preventing you from dropping the cluster and the tables, you can also add the CASCADE CONSTRAINTS clause to the statement:

drop CLUSTER dummy_cluster INCLUDING TABLES CASCADE CONSTRAINTS
/

See message translations for ORA-00951 and search additional resources

ORA-00943: cluster does not exist

Always check out the original article at http://www.oraclequirks.com for the latest comments, fixes and updates.

This is the first of a series of articles regarding Oracle cluster tables.
Index clustered tables and hash clustered tables are covered in detail in the Concepts book and the full syntax of the related commands can be found in the SQL Reference.

There is nothing intriguing in this error, for instance, suppose to execute:

create table dummy_table (
code  number(5,0),
lang  varchar2(30),
descr  varchar2(1024)
) cluster dummy_cluster (code)
/
ORA-00943: cluster does not exist

drop CLUSTER dummy_cluster
/
ORA-00943: cluster does not exist

Evidently, in both cases the dummy cluster did not exist.

The same error is also returned attempting to drop a cluster in another user's schema:

drop CLUSTER user2.dummy_cluster
/
ORA-00943: cluster does not exist

In this case it is necessary to hold DROP ANY CLUSTER privilege.


You can check for the existence of a cluster by querying the following dictionary views:

select * from user_clusters;
select * from all_clusters;
select * from dba_clusters; -- requires DBA or SELECT_CATALOG_ROLE role

See message translations for ORA-00943 and search additional resources