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
-)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s