Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

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

Advertisements

One response to “Oracle 12c temporary tables with enabled temp_undo_enabled parameter.

  1. Alexey February 6, 2017 at 08:02

    Temp undo can’t be supported in a distributed transactions environment. Of cource this should be in documentation. More than that, you could easily get ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KTUGETTEMPUSEGHDR: USN MISMATCH].
    Doc ID 2059486.1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: