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

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

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.



Leave a Reply 10

Your email address will not be published. Required fields are marked *


Mike Yeap

Mike Yeap

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

deivsto

deivsto

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

abhishek

abhishek

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
………
.
……………..

andy

andy

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

khyap

khyap

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

Vladimir

Vladimir

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