Create a Temporary Table in Oracle Database 23ai

Temporary tables provide reusable work areas for data that is needed only during a transaction or database session. Applications commonly use them for staging intermediate calculations, sorting or aggregating results, validating imported data, assembling reports, and processing batches without placing temporary rows in permanent application tables.
A telephone-billing process is a useful example. An application may collect current call records, calculate charges and taxes, validate the totals, and then insert the finalized invoice data into permanent billing tables. The intermediate rows do not need to remain after the billing transaction or session has finished. Oracle temporary tables provide a controlled location for this working data while preserving normal transaction behavior.

A global temporary table is created with the CREATE GLOBAL TEMPORARY TABLE statement. Its definition remains in the database until it is explicitly dropped, but its rows are private to the session that inserted them. The word global refers to the visibility of the table definition, not to the visibility of its data.
Multiple sessions can use the same global temporary table at the same time. Each session sees only its own rows. This design allows an application to use one persistent work-table definition without creating a separate permanent staging table for every user or process.
Oracle allocates temporary storage when the table is populated rather than necessarily allocating a segment when the definition is created. The data may use memory and the user's temporary tablespace according to the operation and the amount of temporary data being processed.

The following documentation-style syntax shows the two row-duration choices:

CREATE GLOBAL TEMPORARY TABLE table_name
(
    column_definitions
)
ON COMMIT { DELETE ROWS | PRESERVE ROWS };

The braces and vertical bar are explanatory notation. They indicate that one alternative must be selected. They are not entered literally in executable Oracle SQL.

Use ON COMMIT DELETE ROWS when the temporary data should exist only for the current transaction:

CREATE GLOBAL TEMPORARY TABLE phone_billing_transaction
(
    customer_id       NUMBER,
    billing_period    VARCHAR2(20),
    transaction_date  DATE,
    transaction_type  VARCHAR2(20),
    duration_seconds  NUMBER,
    charge_amount     NUMBER(10,2),
    tax_amount        NUMBER(10,2)
)
ON COMMIT DELETE ROWS;

The rows are transaction-specific. When the transaction commits, Oracle removes the current session's rows from its temporary instance. This behavior is also the default if the ON COMMIT clause is omitted.
This option is appropriate when all intermediate rows belong to one unit of work and should disappear automatically when that unit of work is committed.

Use ON COMMIT PRESERVE ROWS when the same session must continue using the rows after one or more commits:

CREATE GLOBAL TEMPORARY TABLE phone_billing_session
(
    customer_id       NUMBER,
    billing_period    VARCHAR2(20),
    transaction_date  DATE,
    transaction_type  VARCHAR2(20),
    duration_seconds  NUMBER,
    charge_amount     NUMBER(10,2),
    tax_amount        NUMBER(10,2)
)
ON COMMIT PRESERVE ROWS;

The rows remain available to the current session after a commit. They remain private to that session and are removed when the session ends or when the session explicitly truncates the table.
This option is useful for a multi-step process in which intermediate data must survive several commits before the final result is written to permanent tables.

A billing application can use the session-specific table to stage transactions, calculate totals, and validate the billing period before permanent data is created.
Insert a sample row:

INSERT INTO phone_billing_session
(
    customer_id,
    billing_period,
    transaction_date,
    transaction_type,
    duration_seconds,
    charge_amount,
    tax_amount
)
VALUES
(
    1001,
    '2026-06',
    DATE '2026-06-05',
    'VOICE',
    420,
    12.50,
    0.75
);

Commit the transaction and query the temporary data:

COMMIT;

SELECT customer_id,
       billing_period,
       charge_amount,
       tax_amount
FROM phone_billing_session;

The row remains visible to the current session because the table was created with ON COMMIT PRESERVE ROWS. Another session using the same table definition does not see this row.
The temporary table should hold only intermediate work. Final invoices, customer balances, and auditable billing history belong in permanent application tables.

Temporary-table data participates in transaction processing. Changes can be rolled back, and Oracle maintains the internal information needed to preserve transactional consistency.
Temporary rows are not intended for permanent media recovery in the same way as ordinary application data. However, it is inaccurate to say that temporary table activity produces no undo or redo information under every circumstance. Oracle still records what is required for transaction management, rollback, and internal recovery operations.
For a table created with ON COMMIT DELETE ROWS, a commit removes the current transaction's rows. For a table created with ON COMMIT PRESERVE ROWS, a commit preserves the rows for the remainder of the session.
To remove the current session's temporary rows explicitly, use:

TRUNCATE TABLE phone_billing_session;

The persistent table definition remains available after the truncate operation.

Oracle permits indexes on global temporary tables. The index definition persists, while the index entries follow the same transaction or session duration as the temporary rows.

CREATE INDEX phone_billing_session_ix
    ON phone_billing_session
       (customer_id, billing_period);

An index can improve repeated searches, joins, or aggregations against a large temporary data set. It also adds maintenance work during inserts and updates. Create one only when the expected workload justifies the cost.

Query USER_TABLES to verify the table type and row duration:

SELECT table_name,
       temporary,
       duration
FROM user_tables
WHERE table_name IN
(
    'PHONE_BILLING_TRANSACTION',
    'PHONE_BILLING_SESSION'
)
ORDER BY table_name;

The TEMPORARY column identifies the object as a temporary table. The DURATION value indicates whether its rows are transaction-specific or session-specific.

Temporary tablespaces support transient database work such as sorts, hash operations, temporary table segments, temporary LOB storage, and other intermediate SQL processing.
By default, a global temporary table uses the creating user's default temporary tablespace for its temporary segment. A different temporary tablespace can be specified when the object is created:

CREATE GLOBAL TEMPORARY TABLE billing_work
(
    customer_id    NUMBER,
    charge_amount  NUMBER(10,2)
)
ON COMMIT DELETE ROWS
TABLESPACE temp_work;

This example assumes that TEMP_WORK already exists and that the user has the required privileges.
Temporary LOB data may remain in memory or use temporary tablespace storage depending on its size and the operation being performed. Oracle manages the temporary LOB lifecycle; the administrator manages temporary tablespace capacity, user assignments, and overall resource availability.

Global temporary tables differ from permanent tables in several important ways:

1. Foreign-key constraints are not supported on temporary tables.
2. DDL operations other than TRUNCATE generally require that no session is currently bound to the table.
3. Temporary rows are not preserved by backup and media recovery after a system failure.
4. Each session sees only its own rows even though authorized sessions share the same table definition.
5. Connection pools require careful cleanup because a pooled database session can outlive one application request.
6. Global temporary tables should normally be created during schema deployment rather than repeatedly created and dropped inside each transaction.

Specialized table structures and object types can have additional restrictions. Review the current Oracle Database SQL Language Reference before using partitioning, object-relational features, clusters, nested collections, or specialized indexes with a temporary table.

Session-specific temporary data deserves special attention in Java, application-server, and connection-pool environments. An application request may release a database connection back to the pool without ending the underlying Oracle session. If the table uses ON COMMIT PRESERVE ROWS, rows can remain in that pooled session and become visible to a later request that receives the same connection.
Applications should use clear transaction boundaries and explicit cleanup. Depending on the design, cleanup may include a commit, rollback, or TRUNCATE TABLE before the connection is returned to the pool.

Oracle Database also supports private temporary tables. A private temporary table differs from a global temporary table because both its definition and its rows are visible only within the creating session.
A private temporary table normally uses the configured private-table prefix, which defaults to ORA$PTT_:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_billing_work
(
    customer_id    NUMBER,
    charge_amount  NUMBER(10,2)
)
ON COMMIT PRESERVE DEFINITION;

Private temporary tables use ON COMMIT DROP DEFINITION or ON COMMIT PRESERVE DEFINITION. These clauses control the lifetime of the table definition rather than whether temporary rows are transaction-specific or session-specific.
Use a global temporary table when an application repeatedly uses a known work-table structure shared through one persistent schema definition. Use a private temporary table when the temporary structure itself should exist only within the creating session. Private temporary tables supplement global temporary tables; they do not make them obsolete.

Use ON COMMIT DELETE ROWS when intermediate rows belong to one transaction and should be removed automatically at commit. Use ON COMMIT PRESERVE ROWS when a session must use the same rows across multiple transactions. Use a private temporary table when both the definition and data should remain private to one session.
A permanent staging table may still be more appropriate when data must survive session failure, be shared across sessions, participate in long-term auditing, or remain available for recovery and restart processing.

Click the quiz link below to test your knowledge of Oracle temporary table concepts.
Create Temporary Tables - Quiz
In the next lesson, you will learn how to drop a column from an Oracle table.