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

Breadcrumb

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
( 19.5.0.0.0 ):


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
/

X
---
1


declare
   pragma autonomous_transaction;
begin
   insert into gtt values ( 2 );
   commit;
end;
/

Statement processed.


select * from gtt
/

no data found



Remark:
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:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1086033238621

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:

https://livesql.oracle.com/apex/livesql/s/jgc0expagu7afd36vhmmdwt6i



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

More to Explore

Design

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