Monday, March 31, 2008

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

Always check out the original article at 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

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles