Global Temporary Tables (GTT) are specialized database objects designed to store temporary data that is automatically managed by Oracle. Unlike regular tables, GTT definitions are permanent and visible to all sessions, but the data within them is private to each session or transaction.

Temporary tables are a tool that can be very important for solving performance issues in some very complex queries. Also, these tables can be used to store temporary results from queries that are executed multiple times within code.

Many Oracle DBAs believe that temporary tables are unnecessary and that the Oracle optimizer can efficiently handle all queries, and that solving performance issues is the responsibility of SQL developers (who must write efficient SQL) and DBAs (who must design and manage the database's logical and physical structure—indexes, datafiles, etc.).

While that is partly true, it does not eliminate the fact that in practice there are always exceptions. In fact, Oracle created temporary tables because there are certainly cases where they can be useful! Personally, I believe it's the responsibility of SQL developers and DBAs to discuss the various possible solutions (including temporary tables) and weigh the time and effort required to implement each solution against the expected performance gains.

Oracle allows the creation of two types of temporary tables: transaction-level and session-level temporary tables.

Key Characteristics of GTT:

• Table structure is permanent and shared across all sessions.
  
• Data is private to each session or transaction.
  
• No logging to redo logs (faster DML operations).
  
• Automatic cleanup when session/transaction ends.
  
• Support for indexes, constraints, and triggers.
  
• Statistics are maintained separately for each session.
  

Transaction-level GTTs automatically delete their data when a transaction commits or rolls back. This makes them ideal for storing intermediate results during complex multi-step operations within a single transaction.

CREATE GLOBAL TEMPORARY TABLE table_gtt_transaction
(
    pkid NUMBER(1,0) NOT NULL
,   code VARCHAR2(2) NOT NULL
) ON COMMIT DELETE ROWS;

The ON COMMIT DELETE ROWS clause indicates that the data is private to the current transaction (it is invisible outside that transaction) and will be automatically deleted at the end of the transaction.

By default, if the ON COMMIT clause is not specified, the table is treated as a transaction-level temporary table.

Session-level GTTs preserve data across multiple transactions within the same database session. Data persists until the session ends or is explicitly deleted, making them suitable for longer-term temporary storage needs.

CREATE GLOBAL TEMPORARY TABLE table_gtt_session
(
    pkid NUMBER(1,0) NOT NULL
,   code VARCHAR2(2) NOT NULL
) ON COMMIT PRESERVE ROWS;

The ON COMMIT PRESERVE ROWS clause indicates that the data is private to the current SQL session (it is invisible outside that session) and will be automatically deleted at the end of the session.

Important Session Considerations:
The session refers to the SQL session, not the application session. When a user connects to a web application, the web container (such as Tomcat) may create a unique application session (if configured to do so), and this application session is unrelated to the SQL session created by Oracle.

There are two scenarios to consider when creating a session-level temporary table:

• Several application sessions can share the same SQL session, so inserts made by one user in an application session may be visible to other users who share the same SQL session.
• A user accessing a web application may be routed to a different SQL session for each new HTTP request.