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.

.
.
.