Tuesday, May 24, 2011

Flashback Data Archive 11.1 vs 11.2

Some of the DDL statements that weren't allowed on tables that had FDBA enabled in 11.1 are now allowed on 11.2. Section below gives a summary of the changes.

From 11.1 documentation
DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:

ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or sub partition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
DROP TABLE statement
RENAME TABLE statement
TRUNCATE TABLE statement


On 11.1.0.7 (11.1.0.7.3 PSU)
SQL> create table x (a number, b varchar2(1000)) flashback archive fbdarchive;

Table created.

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


SQL> truncate table x;
truncate table x
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
From 11.2 documentation
DDL Statements on Tables Enabled for Flashback Data Archive

Flashback Data Archive supports many DDL statements, including some that alter the table definition or move data. For example:
ALTER TABLE statement that does any of the following:
Adds, drops, renames, or modifies a column
Adds, drops, or renames a constraint
Drops or truncates a partition or sub partition operation
TRUNCATE TABLE statement
RENAME statement that renames a table

Some DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive. For example:
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 sub partition operation

DROP TABLE statement


On 11.2 (11.2.0.2)
SQL> create table x (a number, b varchar2(1000)) flashback archive auditarchive;

Table created.

SQL> alter table x rename to y;

Table altered.

SQL> truncate table y;

Table truncated.

SQL> drop table y;
drop table y
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
More bugs on 11.1 vs 11.2 Bug : COMMIT DELAY WHEN UPDATING TABLE IN FLASHBACK DATA ARCHIVE MODE 8226666

Bug 9786460 - ORA-600 [qertbfetchbyrowid_fda:no selected row] after bugfix 8226666 [ID 9786460.8]