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


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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google