Global Temporary Table -- revisited

Revisiting the previous case in a 12.2 PDB ....

(This time, the two sessions by "HEMANT" and "SYSTEM" have the Username as the SQL prompt)

In the previous blog post, I demonstrated how to check space allocation for a GTT.   But how does Oracle determine how much space to allocate ?

HEMANT>create global temporary table my_gtt_2
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  /

Table created.

HEMANT>
SYSTEM>select sid,serial# from v$session where username = 'HEMANT';

       SID    SERIAL#
---------- ----------
       300      11923

SYSTEM>
SYSTEM>select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  /      

no rows selected

SYSTEM>

So, the creation of a GTT does not allocate any space. A GTT definition is a logical definition and does not allocate space unless and until rows are inserted.

Let me insert a row and check the space.

HEMANT>insert into my_gtt_2 values (1, 'First Object');

1 row created.

HEMANT>commit;

Commit complete.

HEMANT>
SYSTEM>select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SESSION_NUM SQL_ID        TABLESPACE                     CONTENTS  SEGTYPE       CON_ID SQL_ID_TEMPSE
----------- ------------- ------------------------------ --------- --------- ---------- -------------
HEMANT
      11923 54zdzm1mrqpy9 TEMP                           TEMPORARY DATA               6 54zdzm1mrqpy9


SYSTEM>
SYSTEM>select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('54zdzm1mrqpy9') 
  4  /

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
54zdzm1mrqpy9
insert into my_gtt_2 values (1, 'First Object')


SYSTEM>
SYSTEM>select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         1        128 54zdzm1mrqpy9 54zdzm1mrqpy9

SYSTEM>

So, that is 1MB (128 blocks of 8KB each) for the initial extent.  Why is it so ?

SYSTEM>select extent_management, allocation_type, initial_extent, next_extent
  2  from dba_tablespaces
  3  where tablespace_name = 'TEMP'
  4  /

EXTENT_MAN ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
---------- --------- -------------- -----------
LOCAL      UNIFORM          1048576     1048576

SYSTEM>

Because, by default, a TEMPORARY TABLESPACE is created with 1MB Uniform Extents.

Can I change this ?

SYSTEM>create temporary tablespace small_temp
  2  tempfile '/usr/tmp/small_temp.dbf' size 100M
  3  extent management local uniform size 64K;

Tablespace created.

SYSTEM>select extent_management, allocation_type, initial_extent, next_extent
  2  from dba_tablespaces
  3  where tablespace_name = 'SMALL_TEMP'
  4  /

EXTENT_MAN ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
---------- --------- -------------- -----------
LOCAL        UNIFORM          65536       65536

SYSTEM>
HEMANT>create global temporary table small_gtt
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  tablespace small_temp
  5  /

Table created.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE                     BLOCKS     SQL_ID        SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
TEMP                                  128 fd8qcczn6avw6 54zdzm1mrqpy9


SYSTEM>
HEMANT>insert into small_gtt
  2  values (1, 'First Object');

1 row created.

HEMANT>commit;

Commit complete.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE                     BLOCKS     SQL_ID        SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
TEMP                                  128 2j3pja8qjx1sd 54zdzm1mrqpy9

HEMANT
SMALL_TEMP                              8 2j3pja8qjx1sd 2j3pja8qjx1sd


SYSTEM>
SYSTEM>select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('54zdzm1mrqpy9','fd8qcczn6avw6','2j3pja8qjx1sd')
  4  /

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
2j3pja8qjx1sd
insert into small_gtt values (1, 'First Object')


SYSTEM>

(The previous two SQLs no longer present in the cache but we can see that "54zdzm1mrqpy9" is for the first GTT and "fd8qcczn6avw6" is for the second GTT)

Thus, my existing HEMANT session has two different Temporary Segment usages being reported. That in the TEMP tablespace is 1MB for the 1 row in MY_GTT_2 and that in the SMALL_TEMP tablespace is 64KB for the 1 row in SMALL_GTT.

If I TRUNCATE a GTT (or exit the session) space is released.

HEMANT>truncate table my_gtt_2;

Table truncated.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE                     BLOCKS     SQL_ID        SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
SMALL_TEMP                              8 0zwdmqw9fpkjv 2j3pja8qjx1sd


SYSTEM>

Thus you can have
(a) multiple GTTs (with different definitions, e.g. one for SALES data processing and one for HR data processing)
(b) in multiple TEMPORARY TABLESPACES

The GTT definitions are visible across all sessions that are in the same USER login or have been granted privileges but the data in one session is *not* visible to another session, even if COMMITTed (unlike normal "Permanent" Tables)
.
.
.