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

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

情况分析

置于Flashback data archive中的table的一些限制
追踪表(Tracked table)，也就是指定将历史数据保存到某个flashback data archive中的table，不能执行DDL操作(add column除外)。

NING@11g>drop table test;
drop table test
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
NING@11g>truncate table test;
truncate table test
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
NING@11g>alter table test drop column object_id;
alter table test drop column object_id
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

NING@11g>alter table test add col_test int;
Table altered.
但是可以rename table，这一点和文档上说的不一致

NING@11g>rename test to test1;
Table renamed.
NING@11g>select table_name,flashback_archive_name from dba_flashback_archive_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NAME
—————————— ——————————
TEST1 TEST_ARCHIVE1

解决方案

SQL> alter table emp no flashback archive;

Table altered.

SQL> alter table t no flashback archive;

Table altered.

SQL> alter table NETSTORE_INCOMEEXPENDITURELIST no flashback archive;

Table altered.

再次执行导出schema操作

C:\Users\MaryHu>exp ARCER/ARCER@DB234 FILE=E:\FGPS_20130528.dmp

Export: Release 11.2.0.1.0 - Production on Tue May 28 09:47:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ARCER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ARCER
About to export ARCER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ARCER's tables via Conventional Path ...
. . exporting table      COILS_ASSETS_DEPRECIATION         55 rows exported
. . exporting table              COILS_CODE_ASSESS         38 rows exported
. . exporting table          COILS_COMPANY_LICENSE         16 rows exported
. . exporting table                      COILS_EMP          8 rows exported
. . exporting table               COILS_LOG_ASSESS         14 rows exported
. . exporting table                   COILS_STORES         37 rows exported
. . exporting table           COMPANY_ANNOUNCEMENT          3 rows exported
. . exporting table                   CRM_CUSTOMER         51 rows exported
. . exporting table                            EMP          0 rows exported
. . exporting table                  FGPS_EMPLOYEE         35 rows exported
. . exporting table               FGPS_FINANCEUNIT          3 rows exported
. . exporting table                     FGPS_GROUP         20 rows exported
. . exporting table             FGPS_GROUP_VS_MENU        212 rows exported
. . exporting table                      FGPS_MENU         51 rows exported
. . exporting table                FGPS_MYRECEIVER         40 rows exported
. . exporting table                    FGPS_PEOPLE        800 rows exported
. . exporting table                FGPS_SYSTEMUSER          7 rows exported
. . exporting table                      FGPS_USER         36 rows exported
. . exporting table          FGPS_USERGRANTACCOUNT         30 rows exported
. . exporting table                   FGPS_VOUCHER         68 rows exported
. . exporting table                FRX_DEV_SUGGEST         26 rows exported
. . exporting table              FRX_OA_ATTENDANCE       4434 rows exported
. . exporting table                    FRX_OA_DICT        480 rows exported
. . exporting table            FRX_OA_LOG_TEMPLATE         26 rows exported
. . exporting table                 FRX_OA_WORKLOG         86 rows exported
. . exporting table                       NETSTORE        243 rows exported
. . exporting table NETSTORE_INCOMEEXPENDITURELIST        243 rows exported
. . exporting table                   SYSTEM_GROUP         35 rows exported
. . exporting table                    SYSTEM_MENU         34 rows exported
. . exporting table                   SYS_CATEGORY         33 rows exported
. . exporting table  SYS_GROUP_MENU_CORRESPONDENCE        100 rows exported
. . exporting table                              T          0 rows exported
. . exporting table                WAITTINGHANDLER         55 rows exported
. . exporting table        WAITTING_HANDLER_DETAIL         78 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

如何将已经纳入数据归档区的表解除闪回数据归档?

SQL> alter table emp no flashback archive;

Table altered.

解除之后，再次进行DDL操作（DROP等），就不会报错

SQL> drop table t;

Table dropped.