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

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;