Problem solved
A reader, January 12, 2018 - 3:00 pm UTC
GTT with "on commit preserve rows" always empty after commit IF
temp_undo_enabled sets to true
January 13, 2018 - 3:59 am UTC
I can't reproduce that either ...
SQL> create global temporary table t ( x int ) on commit preserve rows;
Table created.
SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL> alter session set temp_undo_enabled = true;
Session altered.
SQL>
SQL> insert into t
2 select rownum
3 from dual connect by level <= 100;
100 rows created.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
100
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
100
Not a complete test case
Ghassan, January 13, 2018 - 12:10 pm UTC
Requester didn't show whole code. I doubt between the two select another session that defeat gtt purpose. So if the next select is from another session this is the perfect role of gtt.
Example to reproduce behavior against Oracle 12c
Triet Le, August 28, 2018 - 2:28 pm UTC
Original reported behavior is reproducible over remote database link as demonstrated by the example below:
% sqlplus dummyuser/dummy_PW1@ORA12DB1
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 28 08:58:05 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create or replace procedure enable_temp_undo as
2 begin
3 execute immediate 'alter session set temp_undo_enabled = true';
4 end;
5 /
Procedure created.
SQL> create global temporary table gtt (a varchar2(60)) on commit preserve rows;
Table created.
SQL> exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
%
% sqlplus dummyuser2/dummy_PW2@ORA12DB2
SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 28 09:07:19 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Aug 28 2018 09:07:06 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> execute enable_temp_undo@ORA12DB1;
PL/SQL procedure successfully completed.
SQL> insert into gtt@ORA12DB1 select * from dual;
1 row created.
SQL> select * from gtt@ORA12DB1;
A
------------------------------------------------------------
X
SQL> commit;
Commit complete.
SQL> select * from gtt@ORA12DB1;
no rows selected
SQL> exit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options