Did You Know #3 – Temporary Tables

Today, something important about global temporary tables and the space they consume.As we know, global temporary tables contain private data, so data inserted into the table is accessible only from the same session. Because of that, global temporary tables will not appear in dba_segments and won’t consume any space in a tablespace.
Oracle, instead of the standard storage mechanism, uses the temporary tablespace for global temporary tables. Temporary tablespaces contain private data by design. However, if we have a large temporary table, we don’t want it to consume all the space in the temporary tablespace that other sessions use for sorts and joins.
For that reason we can assign the global temporary table to a specific temporary tablespace:

create temporary tablespace temp_data
   tempfile '/opt/oracle/oradata/orcl/temp_data.dbf'
   size 1G;
create global temporary table
   temp_tbl (id number, name varchar2(100))
   tablespace temp_data;

Hope you find this useful.
Liron