Temporary Tables in Oracle SQL

A Temporary Table in Oracle SQL is a special type of table that holds temporary data for the duration of a session or transaction. Temporary tables are widely used for storing intermediate results, processing large datasets, and isolating temporary data from permanent tables.

The key features of Oracle temporary tables are:

1. Private Data: Data in a temporary table is specific to the session or transaction.
2. Automatic Cleanup: Oracle automatically manages the lifecycle of the data.
3. Shared Structure: Multiple users share the same table structure, but the data is isolated.

Global Temporary Tables (GTT)

Oracle implements temporary tables as Global Temporary Tables (GTT). They are defined at the schema level and are shared by all sessions, but the data stored in them is session-specific or transaction-specific.

Global Temporary Tables (GTT) in databases like Oracle are used to store temporary data that is session-specific or transaction-specific. Below are the types of Global Temporary Tables based on their behavior:

1. Transaction-Specific Temporary Tables

Data persists for the duration of a transaction.
Once the transaction ends (commit or rollback), the data in the table is automatically deleted.
Use case: Storing intermediate results for processing within a single transaction.

Syntax:

CREATE GLOBAL TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype
) ON COMMIT DELETE ROWS;

ON COMMIT DELETE ROWS ensures the data is cleared after each transaction.

2. Session-Specific Temporary Tables

Data persists for the duration of the user session.
Data is cleared only when the session ends (disconnect).
Use case: Maintaining data across multiple transactions within the same session.

Syntax:

CREATE GLOBAL TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype
) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS retains the data even after a transaction is committed or rolled back.

Key Features of Global Temporary Tables:

Storage Isolation: Each session has its own private data in the GTT, even though the table structure is shared.
Automatic Data Cleanup: Data is automatically managed (deleted) based on the specified type.
Performance: Improves performance by reducing contention, as data is session/transaction-specific.