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)
.
.
.

Global Temporary Table in a PDB

Where and how is the space consumption for a Global Temporary Table when created in a Pluggable Database ?

In a 12c MultiTenant Database, each Pluggable Database (PDB) has its own Temporary Tablespace. So, a GTT (Global Temporary Table) in a PDB is local to the associated Temporary Tablespace.

Let me be clear.  The "Global" does *not* mean that the table is
(a) available across all PDBs   (it is restricted to that PDB alone)
(b) available to all schemas (it is restricted to the owner schema alone, unless privileges are granted to other database users as well)
(c) data is visible to other sessions (data in a GTT is visible only to that session that populated it)

The "global" really means that the definition is created once and available across multiple sessions, each session having a "private" copy of the data.
The "temporary" means that the data does not persist.  If the table is defined as "on commit delete rows", rows are not visible after a COMMIT is issued.  If the table is defined as "on commit preserve rows", rows remain only for the life of the session.  In either case, a TRUNCATE can also be used to purge rows.


Here, I connect to a particular PDB and create a GTT and then populate it

$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

Table created.

SQL> 
$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

Table created.

SQL> 
SQL> select distinct sid from v$mystat;

       SID
----------
        36

SQL> 
SQL> select serial# from v$session where sid=36;

   SERIAL#
----------
      4882

SQL> 

Another session can see that the table exists (without any corresponding "permanent" tablespace) but not see any data in it.

SQL> select temporary, tablespace_name
  2  from user_tables
  3  where table_name = 'MY_GTT'
  4  /

T TABLESPACE_NAME
- ------------------------------
Y

SQL> select count(*) from my_gtt;

  COUNT(*)
----------
         0

Let's look for information on the Temporary Tablespace / Segment usage(querying from the second session)

SQL> select sid, serial#, sql_id    
  2  from v$session
  3  where username = 'HEMANT';

       SID    SERIAL# SQL_ID
---------- ---------- -------------
        36       4882
       300      34315 739nwj7sjgaxp

SQL> select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage;

USERNAME SESSION_NUM SQL_ID        TABLESPA CONTENTS  SEGTYPE       CON_ID SQL_ID_TEMPSE
-------- ----------- ------------- -------- --------- --------- ---------- -------------
HEMANT          4882 92ac4hmu9qgw3 TEMP     TEMPORARY DATA               6 3t82sphjrt73h

SQL> select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('92ac4hmu9qgw3','3t82sphjrt73h');

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
92ac4hmu9qgw3
select serial# from v$session where sid=36


SQL> 

So, SID 36 is the session that populated the GTT and identified it's own SID (36) and SERIAL# (4882), which we can see as the user of the Temporary Segment when querying from the second session (SID 300).

What about the size of the temporary segment populated by SESSION_NUM (i..e SERIAL#)=4882 ?
Again, querying from the second session.

SQL> select extents, blocks, sql_id, sql_id_tempseg 
  2  from v$tempseg_usage
  3  where session_num=4882;

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         4        512 92ac4hmu9qgw3 3t82sphjrt73h

SQL> 

Now, let's "grow" the GTT with more rows (and then query from the other session).

SQL> insert into my_gtt select * from my_gtt;

72638 rows created.

SQL> 
SQL> l
  1  select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3* where session_num=4882
SQL> /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         8       1024 gfkbdvpdb3qvf 3t82sphjrt73h

SQL> select sql_text from v$sql where sql_id = 'gfkbdvpdb3qvf';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into my_gtt select * from my_gtt

SQL> 

So, the increased space allocation in the Temporary Segment is from the growth of the GTT. Let's grow it further.

SQL> INSERT INTO MY_GTT select * from MY_GTT;

145276 rows created.

SQL> /

290552 rows created.

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

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
        29       3712 2c3sccf0pj5g1 3t82sphjrt73h

SQL> select sql_text, executions from v$sql where sql_id = '2c3sccf0pj5g1';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
EXECUTIONS
----------
INSERT INTO MY_GTT select * from MY_GTT
         2


SQL> 

So, the growth of the GTT results in increased space allocation in the Temporary Segment.

What happens if I truncate the GTT ?

SQL> truncate table my_gtt;

Table truncated.

SQL> 
SQL> select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=4882;

no rows selected

SQL> 
SQL> select * from v$tempseg_usage;

no rows selected

SQL> 

Temp Space is released by the TRUNCATE of the GTT.

I invite you to try this with a GTT created with ON COMMIT DELETE ROWS and see what happens before and after the COMMIT.

.
.
.

New Video : Online Relocation of a Pluggable Database

I have published a new YouTube Video:    Online Relocation of a Pluggable Database.

.
.
.