Oracle 12c temporary tables with enabled temp_undo_enabled parameter.

Oracle 12.1.0.2 with 160719 BP

There is new parameter in 12c temp_undo_enabled, it enables DML on temporary tables to save undo data in temporary tablespace.

Idea is brilliant, but there is a problem about which you should be informed.

Scenario:

• You set temp_undo_enabled parameter to TRUE.
• You created global temporary table with ON COMMIT PRESERVE ROWS option
• You executed distributed transaction, INSERT INTO … SELECT FROM REMOTE@DBLINK
• COMMIT

After COMMIT statement you will not find any rows in your temporary table.

Test case:

alter session set temp_undo_enabled=true;

create global temporary table TEMP_TABLE (
                                col1 number,
                                col2 number,
                                col3 number)
                                ON COMMIT PRESERVE ROWS;

insert into TEMP_TABLE 
   select a, b, c from REMOTE_TAB@TDBLINK where c not in ('SCOTT');

3500 rows created.

commit;

select * from TEMP_TABLE;

no rows selected

It do not behave as expected. As soon as you will disable temporaray undo by setting temp_undo_enabled to FALSE and repeat above testcase everything will work as expected. Create new session after you will change temp_undo_enabled parameter.

 

 

---

(c) Aychin Gasimov, 11/2016, Munich, Germany