ORA-55610: Invalid DDL statement on history-tracked table

If you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem.

SQL> truncate table customer;
truncate table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> drop table customer;
drop table customer
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled.

SQL> -- Check history tracking
SQL> select flashback_archive_name
  2  from dba_flashback_archive_tables
  3  where table_name = 'CUSTOMER';

FLASHBACK_ARCHIVE_NA
--------------------
TOTALRECALL_FA

How do you get around this problem?

A single simple step will give you the solution – dissociate the table from the flashback archive.

SQL> -- Remove history tracking
SQL> -- before dropping table
SQL> alter table customer
  2  no flashback archive;

Table altered.

Drop table now.

SQL> -- Without history tracking
SQL> drop table customer;

Table dropped.