What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled?

C is the correct answer as per http://surachartopun.com/2010/06/ddl-on-tables-enabled-for-flashback.html

16.9.6 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

C is the final answer.

C – OK
I test this and record in archive is not truncated !!! ( erlest record truncated are in archive to )

create tablespace fba datafile ‘c:\fba.dbf’ size 50m;
create flashback archive FBDA1 Tablespace fba retention 1 year;
create table mm1 (d number(5));
alter table mm1 flashback archive FBDA1;

SQL> select * from mm1;

no rows selected

SQL> insert into mm1 (d) values (8);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mm1;

D
———-
8

SQL> truncate table mm1;

Table truncated.

SQL> select * from mm1;

no rows selected

SQL> SELECT * FROM mm1 AS OF TIMESTAMP TO_TIMESTAMP (‘2015-04-07 14:14:00’, ‘YYYY-MM-DD HH24:MI:SS’);

no rows selected

SQL> SELECT * FROM mm1 AS OF TIMESTAMP TO_TIMESTAMP (‘2015-04-07 14:13:00’, ‘YYYY-MM-DD HH24:MI:SS’);

no rows selected

SQL> SELECT * FROM mm1 AS OF TIMESTAMP TO_TIMESTAMP (‘2015-04-07 14:15:00’, ‘YYYY-MM-DD HH24:MI:SS’);

D
———-
8

SQL> select * from mm1;

no rows selected

C

DDL Restrictions

There are some restrictions on the DDL statements that can be used on tables that have flashback archiving enabled and the restrictions are different between 11gR1 and 11gR2.

If you are using Oracle Database 11gR1 using any of the following DDL statements on table with flashback data archive enabled will result in error ORA-55610.

ALTER TABLE statements that DROP, RENAME or MODIFY a column.
ALTER TABLE statements that perform partition or sub-partition operations.
ALTER TABLE statements that either add or rename a constraint.
ALTER TABLE statements which Includes an UPGRADE TABLE with or withINCLUDING DATA clause
TRUNCATE TABLE
RENAME TABLE
DROP TABLE

In Oracle Database 11gR2 the following DDL statements that could not performed on table with flashback data archive enabled can now be performed without error.

ALTER TABLE statements that DROP, RENAME or MODIFY a column.
ALTER TABLE statements that drop or truncate a partition or sub-partition.
ALTER TABLE statements that either add or rename a constraint.
TRUNCATE TABLE
RENAME TABLE

There are still some DDL statements that will result in error ORA-55610 in Oracle Database 11gR2.

ALTER TABLE statements which Includes an UPGRADE TABLE with or with INCLUDING DATA clause
ALTER TABLE statements that move or exchange a partition or sub-partition
DROP TABLE

http://www.oracledistilled.com/oracle-database/flashback-data-archive-oracle-total-recall/

Answer is C

Oracle is smart enough to archive the data before executing the truncation on the table.

http://www.dbaglobe.com/2014/08/1z0-060-upgrade-to-oracle-12cflashback.html

oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 4 07:21:18 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:
Last Successful login time: Mon Aug 04 2014 07:19:37 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create tablespace fbda_1y datafile ‘/u01/app/oracle/oradata/orcl/fbda_1y.dbf’ size 10M autoextend on;

Tablespace created.

SQL> create flashback archive FBDA1 tablespace fbda_1y quota 1G retention 1 year;

Flashback archive created.

SQL> create table emp1 tablespace users flashback archive FBDA1
2 as select * from hr.employees where 1=2;

Table created.

SQL> alter flashback archive FBDA1 set default;
alter flashback archive FBDA1 set default
*
ERROR at line 1:
ORA-55611: No privilege to manage default Flashback Archive

SQL> conn / as sysdba
Connected.
SQL> alter flashback archive FBDA1 set default;

Flashback archive altered.

SQL> conn donghua
Enter password:
Connected.
SQL>

SQL> col flashback_archive_name for a30
SQL> select owner_name, flashback_archive_name, status
2 from dba_flashback_archive;

OWNER_NAME FLASHBACK_ARCHIVE_NAME STATUS
—————————— —————————— ——-
DONGHUA FBDA1 DEFAULT

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
———————– —————— —————- ————-
FBDA1 1 FBDA_1Y 1024

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
——————– ———- ———————– ——————- ——–
EMP1 DONGHUA FBDA1 SYS_FBA_HIST_109887 ENABLED

SQL> insert into emp1
2 select * from hr.employees where employee_id=107;

1 row created.

SQL> commit;

Commit complete.

SQL> select first_name,last_name from emp1 where employee_id=107;

FIRST_NAME LAST_NAME
——————– ————————-
Diana Lorentz

SQL> truncate table emp1;

Table truncated.

SQL> select * from emp1;

no rows selected

SQL> select * from emp1 versions between scn minvalue AND maxvalue;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
———– ——————– ————————- ————————- ——————– ———
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
———- ———- ————– ———- ————-
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07
IT_PROG 4200 103 60

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

SQL> alter table emp1 drop column email;
SQL> alter table emp1 drop column hire_date;
SQL> alter table emp1 drop column job_id;
SQL> alter table emp1 drop column salary;
SQL> alter table emp1 drop column commission_pct;
SQL> alter table emp1 drop column manager_id;
SQL> alter table emp1 drop column department_id;
SQL> alter table emp1 add (note varchar2(5));

SQL> insert into emp1 values(1,’Donghua’,’Luo’,’12c’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp1;

EMPLOYEE_ID FIRST_NAME LAST_NAME NOTE
———– ——————– ————————- —–
1 Donghua Luo 12c

SQL> select * from emp1 versions between scn minvalue AND maxvalue;

EMPLOYEE_ID FIRST_NAME LAST_NAME NOTE D_4956408_EMAIL D_4956474_PHONE_NUMB
———– ——————– ————————- —– ————————- ——————–
D_4956528 D_4956605_ D_4956645_SALARY D_4956689_COMMISSION_PCT D_4956735_MANAGER_ID D_4956780_DEPARTMENT_ID
——— ———- —————- ———————— ——————– ———————–
107 Diana Lorentz DLORENTZ 590.423.5567
07-FEB-07 IT_PROG 4200 103 60

1 Donghua Luo 12c

What is the result of executing a TRUNCATE TABLE command on a table that has Flashback Archiving enabled? Oracle is smart enough to archive the data before executing the truncation on the table.

C

In 12c DDL Statements on Tables Enabled for Flashback Data Archive support TRUNCATE TABLE statement

for me it’s a logical choice: TRUNCATE would work and archive all the lines. I did a simple test that confirmed this behavior. So, C is correct.

c is correct
http://www.dbaglobe.com/2014/08/1z0-060-upgrade-to-oracle-12cflashback.html