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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s