ORA-55610: Invalid DDL statement on history-tracked table (flashback archive)

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;