Global Temporary Tables

(Oracle 8i) is considered to be vital for DBAs and developers.

What are Global Temporary Tables:

Data in this table is stored as long as the session or transaction gets over and is private for each session. eventually, after disconnection, the data is lost but the definition of the table remains. Performing ddl operations is not necessary, however.

The synonyms, or views and other structures related to the temporary table don’t disappear after transaction or session. Indexes will be created likewise. The features of Temporary tables have triggers and all the statistics related to table access cost, information of rows and blocks, join cardinality, etc. Foreign keys cannot be integrated into temporary tables.

Examples of usage involves replicated data manipulation from remote tables, it is also useful in temporarily storing a set of rows to be processed against other tables.

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.

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.

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.

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.

To read more on Global Temporary table in Oracle, read here: https://www.edupristine.com/blog/oracle-global-temporary-tables