ORA-55610: Invalid DDL statement on history-tracked table解决
今天是2013-10-28，刚刚我进行实验的时候发现不能删除一个表，随即模拟了一下过程：
如下：
SQL> drop table rhys.amy;
drop table rhys.amy
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL>
查看错误信息：
SQL> !oerr ora 55610
55610, 00000, "Invalid DDL statement on history-tracked table"
// *Cause: An attempt was made to perform certain DDL statement that is
// disallowed on tables that are enabled for Flashback Archive.
// *Action: No action required.
//
SQL>
提示说这个表存在flashback archive。查看dba_flashback_archive视图发现如下：
SQL> select owner_name,flashback_archive_name,create_time,status from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME CREATE_TIME STATUS
-------------------- ---------------------------------------- --------------------------------------------------------------------------- -------
SYS ARCHIVE_1 28-JAN-13 11.31.46.000000000 PM
SYS ARCHIVE_DEFAULT 28-JAN-13 11.14.29.000000000 PM DEFAULT
SQL>
发现还真是之前搞的来着。
解决办法：
取消该表的flashback archive功能即可：
SQL> alter table rhys.amy no flashback archive;
Table altered.
SQL>
SQL> drop table rhys.amy;
Table dropped.
SQL>
做到这里在复习一下之前研究的这个功能的相关命令吧。如下所示：
1）创建表空间
SQL> create tablespace fdba datafile '/opt/app/oracle/oradata/fdba.dbf' size 200m autoextend off segment space management auto;
Tablespace created.
SQL> create tablespace fdba_1 datafile '/opt/app/oracle/oradata/fdba1.dbf' size 20m autoextend off segment space management auto;
Tablespace created.
2）创建闪回归档
SQL> create flashback archive archive_1 tablespace fdba quota 100m retention 20 day;
Flashback archive created.
创建默认闪回归档，必须使用sys用户：
SQL> show user
USER is "RHYS"
SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO RHYS;
Grant succeeded.
SQL> CREATE FLASHBACK ARCHIVE DEFAULT ARCHIVE_DEFAULT TABLESPACE FDBA_1 RETENTION 1 YEAR;
CREATE FLASHBACK ARCHIVE DEFAULT ARCHIVE_DEFAULT TABLESPACE FDBA_1 RETENTION 1 YEAR
*
ERROR at line 1:
ORA-55611: No privilege to manage default Flashback Archive
SQL> create flashback archive default archive_default tablespace fdba_1 retention 1 year;
Flashback archive created.
SQL> show suer
SP2-0158: unknown SHOW option "suer"
SQL> show user
USER is "SYS"
SQL>
3）为闪回归档添加表空间
SQL> alter flashback archive archive_1 add tablespace rhys quota 1M;
Flashback archive altered.
SQL>
4）在闪回归档中移除表空间
SQL> alter flashback archive archive_1 remove tablespace rhys;
Flashback archive altered.
SQL>
5）修改闪回归档占用表空间配合。
SQL> alter flashback archive archive_1 modify tablespace fdba quota 200M;
Flashback archive altered.
SQL>
6）修改闪回归档中保留期限
SQL> alter flashback archive archive_1 modify retention 20 day;
Flashback archive altered.
SQL>
7）使用闪回归档
SQL> create table t (
2 a number,
3 b varchar2(20)
4 )
5 flashback archive archive_1;
Table created.
SQL> alter table rhys.emp flashback archive archive_1;
Table altered.
SQL>
8）删除闪回归档数据：
eg：
删除一段时间之前的数据
alter flashback archive archive_1 purge before timestamp to_timestamp('2013-10-28 23:27:00','YYYY-MM-DD HH24:MI:SS');
ALTER FLASHBACK ARCHIVE ARCHIVE_1 PURGE BERFOR SCN 100000000;
删除一天内的数据
ALTER FLASHBACK ARCHIVE ARCHIVE_1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP-INTERVAL '1' DAY);
删除所有的数据
alter flashback archive archive_1 purge all;
9)删除闪回归档
drop flashback archive archive_1;
10）取消表的flashback archive功能：
alter table rhys.amy no flashback archive;
11)闪回归档常用视图；
dba_flashback_archive
dba_flashback_archive_tables;
