What is the result of executing a TRUNCATE TABLE command on a table that has Flashback
Archiving enabled?
A.
It fails with the ORA-665610 Invalid DDL statement on history-tracked message
B.
The rows in the table are truncated without being archived.
C.
The rows in the table are archived, and then truncated.
D.
The rows in both the table and the archive are truncated.
Explanation:
* 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 subpartition 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
* After flashback archiving is enabled for a table, you can disable it only if you either have the
FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. While
flashback archiving is enabled for a table, some DDL statements are not allowed on that table.
C is correct answer
DROP statement will trigger ORA-55610
A STUPID
C is correct:
DDL Statements on Tables Enabled for Flashback Data Archive
Flashback Data Archive supports only these DDL statements:
•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 subpartition operation
•TRUNCATE TABLE statement
•RENAME statement that renames a table
Flashback Data Archive does not support DDL statements that move, split, merge, or coalesce partitions or subpartitions, move tables, or convert LONG columns to LOB columns.
For example, the following DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive:
•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
If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive. To reassociate the Flashback Data Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.
The correct is C.
SQL> alter table oe.orders add period for teste
2 ;
Table altered.
SQL> insert into oe.orders values (2,2,2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table oe.orders;
Table truncated.
SQL> select count(*) from oe.orders;
COUNT(*)
———-
0
SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time(‘ALL’);
PL/SQL procedure successfully completed.
SQL> select count(*) from oe.orders
2 ;
COUNT(*)
———-
0
Correct answer is C. Truncate is supported.
C