Skip to Main Content
  • Questions
  • Changed behavior for transaction level gtt's ?


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Iudith.

Asked: January 07, 2020 - 4:15 pm UTC

Answered by: Connor McDonald - Last updated: January 14, 2020 - 5:05 am UTC

Category: PL/SQL - Version: 19c

Viewed 100+ times

You Asked

Hello All,

First of all, thanks a lot for all your help and have a Great New Year 2020 :)

I would like to bring to your attention the following behavior, which I tested in LiveSQL
( ):

create global temporary table gtt ( x int ) on commit delete rows

Table created.

insert into gtt values (1)

1 row(s) inserted.

select * from gtt


   pragma autonomous_transaction;
   insert into gtt values ( 2 );

Statement processed.

select * from gtt

no data found

In previous versions, starting from 8i up to 11g, the autonomous transaction above was raising the following error:

ORA-14450: attempt to access a transactional temp table already in use

This behavior is also described in the following thread:

as well as in Tom Kyte's book "Expert One-on-One Oracle".

In version 19c (and possibly also in 18c and 12c), the behavior has changed.

Now the autonomous transaction does NOT raise error any more, but the COMMIT (or ROLLBACK) that ends the autonomous transaction also ends the main transaction, which is quite unusual,
and as a result the uncommitted row inserted by the main transaction was also deleted.

The error ORA-14450 is still documented in version 19c identically to the older versions.

I would like to ask whether this behavior change is deliberate, or it might happen to be a bug that deserves a check ?

Here is a test script to reproduce this case:

Thanks a lot in advance & Best Regards,
Iudith Mentzel

with LiveSQL Test Case:

and we said...

Definitely from 12.2 onwards I'm seeing the behaviour you reported.

I'm asking around internally, but I feel it might be a bug.

I'll add more info as it comes to hand.

Update Jan 14:

Bug 30759170 has been logged for this

Is this answer out of date? If it is, please let us know via a Review

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.