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:
You cannot roll back a TRUNCATE TABLE statement, nor can you use a
FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
B
https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS640
http://docs.oracle.com/database/121/SQLRF/statements_10007.htm#SQLRF01707
Although the documentation said it is supported, but my testing shows the table is not allowed to be truncated:
SQL> alter table tab1 flashback archive fbda1;
Table altered.
SQL> truncate table tab1;
truncate table tab1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
C is correct
===============
SQL> select * from test;
C1
———-
6
7
SQL> commit;
Commit complete.
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
2015-09-03 16:10:57.660000 +03:00
SQL> select systimestamp from dual;
SYSTIMESTAMP
—————————————————————————
2015-09-03 16:12:32.285000 +03:00
SQL> truncate table test;
Table truncated.
SQL> SELECT * FROM test AS OF TIMESTAMP TO_TIMESTAMP (‘2015-09-03 16:10:57’, ‘YYYY-MM-DD HH24:MI:SS’);
C1
———-
6
7
SQL> select * from test;
no rows selected
SQL> select * from v$version;
BANNER
——————————————————————————–
CON_ID
———-
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
C
Option C is correct.
Ref : I think people have misinterpreted the Oracle doc
https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS640
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
………
.
……………..
C
Tested on 12c:
You cannot roll back a TRUNCATE TABLE statement.
CREATE TABLE employee (EMPNO NUMBER(4), ENAME VARCHAR2(10)) tablespace fda_ts FLASHBACK ARCHIVE fla1;
ALTER TABLE employee FLASHBACK ARCHIVE;
alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;
INSERT INTO EMPLOYEE VALUES (1, ‘pippo’); commit;
select sysdate from dual;
SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP (’16/03/2016 04:26:11′,’dd/mm/yyyy hh24:mi:ss’);
EMPNO ENAME
—– ——
1 pippo
insert into employee values (2, ‘pluto’); commit;
SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP (’16/03/2016 04:26:11′,’dd/mm/yyyy hh24:mi:ss’);
EMPNO ENAME
—– ——
1 pippo
select OWNER, SEGMENT_NAME from dba_segments where TABLESPACE_NAME=’FDA_TS’;
OWNER SEGMENT_NAME
———– ————–
TEST EMPLOYEE
SQL> truncate table EMPLOYEE;
Table truncated.
SQL> SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP (’16/03/2016 04:26:11′,’dd/mm/yyyy hh24:mi:ss’);
*
ERROR at line 1:
ORA-01466: unable to read data – table definition has changed
B
why I test but succeed?
i.e. C. The rows in the table are archived, and then truncated.
Here is exact syntax I used
conn sys/sys@pdb1 as sysdba
CREATE TABLESPACE fda_ts
DATAFILE ‘E:\ORACLE\data\ORCL\pdb1\fda_ts.dbf’
SIZE 1M AUTOEXTEND ON NEXT 1M;
ALTER USER hr QUOTA UNLIMITED ON fda_ts;
CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
QUOTA 1G RETENTION 1 month;
GRANT FLASHBACK ARCHIVE ON fda_1year TO hr;
GRANT FLASHBACK ARCHIVE ADMINISTER TO hr;
GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO hr;
GRANT CREATE ANY CONTEXT TO hr;
conn hr/hr@pdb1
create table emp2 as select * from employees;
alter table emp2 flashback archive;
select * from emp2 where rownum some data
select sysdate from dual;
SYSDATE
—————-
2016NOV02 065126
truncate table emp2;
SELECT * FROM emp2 where rownum no rows selected
SELECT * FROM emp2
AS OF TIMESTAMP TO_TIMESTAMP (‘2016NOV02 065126’, ‘YYYYMONDD HH24MISS’)
where rownum some data
Answer = C
C
https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS640
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