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.