Consider the following situation - a customer goes to a travel website to find out the different options for travelling to his vacation spot. For this, he/she creates a number of iteneraries out of which only one will be finalized at the end or all of them will be discarded. These iteneraries donot need to be stored in the database on a permanent basis. Storing them in a temporary table to be discarded/deleted at the end of session/transaction makes sense in such cases.

Simply put, temporary tables are used to store data temporarily during a transaction or session.

The data in a temporary table is visible only to the session which inserted that data. However, the table's definition is visible to all sessions.

Syntax:

CREATE GLOBAL TEMPORARY TABLE tbl_emp_temp
(
emp_name varchar2(30),
emp_code number(6),
leave_status boolean
)
ON COMMIT DELETE ROWS;

Here:
CREATE GLOBAL TEMPORARY TABLE is used to create a temporary table.

ON COMMIT is used to specify if the data in the temporary table is session specific or transaction specific.

It has the following options:

DELETE ROWS- creates a transaction specific temporary table. The data in the table is truncated after each commit.
PRESERVE ROWS- creates a session specific temporary table. The data in the table is truncated when the session terminates.

Quick tips:

You can create temporary index on a temporary table which is also transaction or session specific depending on the table.
The data in a temporary table, in general, cannot be recovered in case of a system failure as it is temporary.
Only one transaction is allowed in a transaction specific temporary table at a time.

Temporary tables are stored in the user's default temporary tablespace. But, you can store the table in another temporary tablespace upon creation.

Example:

CREATE GLOBAL TEMPORARY TABLE tbl_emp_temp
(
emp_name varchar2(30),
emp_code number(6),
leave_status boolean
)
ON COMMIT DELETE ROWS
TABLESPACE tbs_temp1;

Advantages of temporary tables:
They are useful in situations where you need to pull data from multiple tables, work on them and later keep only a part of the result set.
They are easy to code and execute more quickly than normal tables.

Disadvantages:
You cannot perform DML operations on temporary tables inside user defined functions.

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com