Oracle Temporary tables - Features and Effective Use

Temporary Tables

Oracle Database temporary tables hold data that exists only for the duration of a transaction or session. Data in a temporary table is private to the session, which means that each session can only see and modify its own data.Temporary tables are useful in applications where a result set must be buffered.

Temporary table features

  • Data in temporary tables is stored in temp segments in the temp tablespace.
  • Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
  • Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session. Indexes can however only be created on empty tables !!
  • Views can be created against temporary tables and combinations of temporary and permanent tables.

Is it a good practice to truncate GTT ?

NO. truncate is DDL.  DDL is expensive.  We are not recommending to turn all the truncates to DELETE’s, because that would be worse. The point is to avoid truncating or deleting in the first place. The effective use of a GTT is to materialize the data for a specific task and have the data automatically purged when you no longer need it.  Use a transaction based temporary table and upon commit, it'll empty itself.