347 647 9001+1 714 797 8196Request a Call
Call Me

A Brief Overview of Oracle Global Temporary Tables (GTT)

August 5, 2015
, , , , , ,

A Brief Overview of Oracle Global Temporary Tables (GTT)

There is no denying that Oracle database has evolved over the years. Some redundant features have paved way for ingenious additions to which Oracle Global Temporary Tables (GTT) is one of the latest. GTT came with Oracle 8i, but within a fairly short time, it has become indispensible for database administration and management

database administration and management

Global Temporary Tables in Brief

This is a DBA tool that is invaluable where complex calculations are involved. The session-specific table is used to store temporary data which cannot be shared with other sessions. In essence, the data in the table is valid only for the session hence the term temporary.

In other words, the data in the table is what is temporary and will disappear when the rows are explicitly deleted. If you commit the current transaction or roll it back, the data is also lost, but the definition of the table stays. Other structures within the table will also not disappear including synonyms and views.

The crux of an Oracle GTT is the fact that the table is private to your session. For instance, 10 applications could be inserting data into the temporary table, but each application can only view its own data.

global temporary tables

Features in an Oracle GTT

The features in this database application make its functional value in DBA invaluable. Most of the features are just like what you find in ordinary tables including triggers, information about rows and columns and much more.

Some of the miscellaneous features you will find in this application include:

  • In case of a truncate statement being issued, only the specific session’s data is affected.
  • Table data storage is in temp segments in the temp table spaces.
  • When a database session ends, the data is automatically deleted.
  • GTT supports indexes and their content and scope is similar to the dataset session.
  • You can create views against temporary tables and a combination of GTT and permanent tables.
  • Statistics on Oracle Global Temporary Tables are common to all sessions.

Creation of Oracle Global Temporary Tables is similar to that of an ordinary table but with the addition of the words GLOBAL TEMPORARY. You should then specify whether the table is transaction bound or session bound in the clause ON COMMIT.

Reasons an Oracle Global Temporary Table is Invaluable

There are myriad situations calling for a temporary table. Moreover, most remote database services cite various benefits of applying GTT in database management. Here are a few of these pros:

  • Enhancing security and privacy: In sensitive businesses, GTT is invaluable because it only saves data for the session and the same cannot be queried by other users.
  • Quicker data retrieval: During a session, you can store your data in a tabular form in the temporary table and access it instantly when you need it.
  • Non-interference between different sets of data as each session’s data is deleted on closure or committal.
  • Ease in getting rid of scratch data by truncating explicitly without affecting anyone else.
  • Best in conveying data among triggers during a session or transaction.
  • Less generation of redo and undo overhead when using a GTT.

There are many other functional benefits of using Oracle Global Temporary Tables, and with this hindsight, you can start applying it on your database.


About the Author

Jenny Richards is content marketer for which is one of the leading companies that provides excellent remote DBA support services.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 78917