Skip to Main Content
  • Questions
  • Global temporary table clears on commit

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Alex.

Asked: May 24, 2016 - 2:24 pm UTC

Last updated: August 29, 2018 - 6:59 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi,
Please, help me to understand - whats happenging?:
I've GTT with ON COMMIT PRESERVE ROWS

and after inserting values with procedure:
"
insert into gtt ... select ...;
commit;
"
next sql
"
select * from gtt
"
returns nothing!!!
but, without commit there are rows

SQL for creating table:
CREATE GLOBAL TEMPORARY TABLE GTT
(
K010 NUMBER(9, 0)
, K020 NUMBER(10, 0)
, CLI_BIZN NUMBER(1, 0) NOT NULL
, CLI_SEGM VARCHAR2(3 BYTE) NOT NULL
)
ON COMMIT PRESERVE ROWS;

and Connor said...

Sorry I can't reproduce (I'm on 12.1.0.2)

SQL> CREATE GLOBAL TEMPORARY TABLE GTT
  2  (
  3  K010 NUMBER(9, 0)
  4  , K020 NUMBER(10, 0)
  5  , CLI_BIZN NUMBER(1, 0) NOT NULL
  6  , CLI_SEGM VARCHAR2(3 BYTE) NOT NULL
  7  )
  8  ON COMMIT PRESERVE ROWS;

Table created.

SQL>
SQL> insert into gtt
  2  select 1,2,3,4 from dual;

1 row created.

SQL>
SQL> select * from gtt;

      K010       K020   CLI_BIZN CLI
---------- ---------- ---------- ---
         1          2          3 4

1 row selected.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from gtt;

      K010       K020   CLI_BIZN CLI
---------- ---------- ---------- ---
         1          2          3 4

1 row selected.


Can you send us an end-to-end test case to use ?

Also, put a trace on the session to make sure nothing 'hidden' is going on (triggers etc), ie,

exec dbms_monitor.session_trace_enable
insert into gtt...
select * from gtt...
exec dbms_monitor.session_trace_disable

and have a look in the trace file, or send it to us: asktom_us@oracle.com

Rating

  (3 ratings)

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

Comments

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
Connor McDonald
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
Connor McDonald
August 29, 2018 - 6:59 am UTC

It's right there in the docs

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html

"Temporary tables are subject to the following restrictions:
...
Distributed transactions are not supported for temporary tables.
...
"

More to Explore

Design

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