How do you create temporary tables in Oracle?

Oracle database allows us to create Temporary tables of two types:

1- Transaction Specific Temporary Table
2- Session Specific Temporary Table

A transaction specific temporary table holds data when a transaction begins with execution of first SQL statement and ends either by commit or rollback. The following command creates a transaction specific temporary table.

CREATE global TEMPORARY TABLE temp_table
  (
     emp_id   NUMBER,
     emp_name VARCHAR2(50)
  )
ON COMMIT DELETE ROWS;

Note :- If "ON COMMIT" clause is omitted, by default oracle creates a transaction specific temporary table.

A session specific temporary table holds data until session lasts. When a commit is performed on session specific temporary table, data is preserved in table. But the data is not visible to others session. The data is lost when session ends.

Session specific temporary table can be created using “PRESERVE ROWS” in ON COMMIT clause.

CREATE global TEMPORARY TABLE temp_table
  (
     emp_id   NUMBER,
     emp_name VARCHAR2(50)
  )
ON COMMIT preserve ROWS;

The definition of temporary table is visible to all the sessions. Unlike permanent table, segment for temporary table is allocated when first INSERT statement is executed on temporary table. Indexes can also be created on temporary tables. The scope and life time of these indexes is similar to temporary tables.