DEV Community

Pranav Bakare
Pranav Bakare

Posted on

1 2 1 1 1

Temporary Tables in Oracle SQL

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

đź‘‹ Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay