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

advertisements

_____________________________________________________________________________________________________________________

Error Description:

Truncate table command failed with following error message.

SQL> TRUNCATE TABLE DEPT;

TRUNCATE TABLE DEPT

               *

ERROR at line 1:

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

Solution Description

The table which you are going to truncate is a clustered table. In clustered tables the data is stored together in more efficient manner. In clustered tables the related rows of different tables saved together in same data blocks. This will reduce the I/O for the joins of clustered tables. Also, it improves the access time.

So when you do a truncate on the clustered tables it will try to erase the allocated blocks which are already shared with other tables. So it is not allowed and Oracle will throw this error. These are the options which you can perform the clustered tables.

1. You can easily delete the rows from the table by using delete command.
2. Drop single table and recreate it with cluster.
3. Truncate cluster command will erase all the tables attached to the particular cluster.

SQL> TRUNCATE TABLE DEPT;

TRUNCATE TABLE DEPT

               *

ERROR at line 1:

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

SQL> delete from dept;

4 rows deleted.

SQL>

SQL> rollback;

Rollback complete.

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

SQL> select count(*) from dept;

  COUNT(*)

----------

         4

SQL> truncate cluster emp_dept;

Cluster truncated.

SQL> select count(*) from emp;

  COUNT(*)

----------

         0

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

_____________________________________________________________________________________________________________________

Website Stats