DDL on Tables Enabled for Flashback Data Archive

On 11gR2, Flashback Data Archive supports many DDL statements. A Flashback Data Archive is feature on 11g.

SQL> create table tb_recall (id number, name varchar2(50));

Table created.

SQL> alter table tb_recall flashback archive fla1;

Table altered.

SQL> insert into tb_recall values (1,'surachart');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tb_recall add (address varchar2(100));

Table altered.

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

SQL> alter table tb_recall rename to tb_recall_new;
alter table tb_recall rename to tb_recall_new
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

can't use "TRUNCATE", "RENAME" table on 11gR1, then on 11gR2:

SQL> create tablespace tbs_fla1 datafile size 1G;

Tablespace created.

SQL> create flashback archive fla1 tablespace tbs_fla1 retention 1 year;

Flashback archive created.

SQL> insert into tb_recall values ('1','surachart');

1 row created.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 15:45:00 ICT 2010

SQL> alter table tb_recall add (address varchar2(100));

Table altered.

SQL> truncate table tb_recall;

Table truncated.

SQL> alter table tb_recall rename to tb_recall_new;

Table altered.

SQL> select count(*) from tb_recall_new;

COUNT(*)
----------
0

SQL> select count(*) from tb_recall_new as of timestamp to_timestamp ('2010-06-03:15:45:00', 'yyyy-mm-dd:hh24:mi:ss');

COUNT(*)
----------
1

We can use truncate and rename table.

SQL> alter table tb_recall_new add (zip varchar2(10));

Table altered.

SQL> insert into tb_recall_new values(1,'surachart','999','10400');

1 row created.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 16:01:41 ICT 2010

SQL> alter table tb_recall_new drop column zip;

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS
---------- ------------------------------ ---------------
1 surachart 999

didn't see column(ZIP), then checked at '2010-06-03:16:02:00'

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:02:00', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS ZIP
---------- ------------------------------ --------------- ----------
1 surachart 999 10400

How? if we re- add column (old name).

SQL> select * from tb_recall_new ;

ID NAME ADDRESS A
---------- ------------------------------ --------------- ----------
1 surachart 999

SQL> update tb_recall_new set A=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 16:17:46 ICT 2010

SQL> alter table tb_recall_new drop column A;

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS
---------- ------------------------------ ---------------
1 surachart 999

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:17:46', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS A ZIP
---------- ------------------------------ --------------- ---------- ----------
1 surachart 999 100

then added old column name.

SQL> alter table tb_recall_new add (a number);

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS A
---------- ------------------------------ --------------- ----------
1 surachart 999

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:17:46', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS A ZIP
---------- ------------------------------ --------------- ---------- ----------
1 surachart 999

After dropped and re- added column (old name), that made us don't see old data... Any Idea?

Some DDL statements cause error ORA-55610:
- ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
- ALTER TABLE statement that moves or exchanges a partition or subpartition operation
- DROP TABLE statement

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

-)