Temporary Table in Oracle

Temporary table is the table exists in memory for the duration of session or transaction as declared.
Data in temporary table is private to the session

You can create temporary table as
1. Global temporary table
2. Private temporary table

Difference between Global And Private temporary table
1. Global name same as permanent table V/S Private table have prefix “ORA$PTT_”.
2. Global table is available to all sessions V/s Private table at session level on which its created.
3. Global table stored at DISK V/s Private table is in memory.

Created at two levels:

Session Level
During the session the data in table remains private. At the end of the session, the session related data is dropped.

Transactional Level (Default)
A transaction-specific temporary table allows only one transaction at a time. If there are several transactions in a single transaction scope, each autonomous
transaction can use the table only as soon as the previous one commits.

Note: On roll-back, temporary table data is lost, only table definition present

Global Temporary Table
Global temporary table is stored on disk like permanent tables and visible to all connected sessions.
The definition of a global temporary table is visible to all the sessions but its content is local to a session.
Data in a global temporary table is visible only to the session that inserts the data into the table.

Example for Transaction Specific:

CREATE GLOBAL TEMPORARY TABLE trans_temp
( id number,
trandate DATE,
createdate DATE,
amount number(20,2))
ON COMMIT DELETE ROWS;

Example for session specific:

CREATE GLOBAL TEMPORARY TABLE trans_temp
( id number,
trandate DATE,
createdate DATE,
amount number(20,2))
ON COMMIT PRESERVE ROWS;

Note: Indexes can be created on global temporary tables

Private Temporary Tables
Private temporary tables are temporary database objects stored in memory.
These are dropped at the end of a transaction or session. It is visible to only that session for which it created.
Private temporary table is used for fast access because it is placed in memory.
Suppose you have reporting application, it fetch data from the database for report and do calculation and save data temporary for that particular session.

Example for Transaction Specific:

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_trans_temp
( id number,
trandate DATE,
createdate DATE,
amount number(20,2))
ON COMMIT DROP DEFINITION;

Example for session specific:

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_trans_temp
( id number,
trandate DATE,
createdate DATE,
amount number(20,2))
ON COMMIT PRESERVE DEFINITION;