benefits of temporary table in oracle over an ordinary table

Question

I came accross creating the temporary table in oracle. But could not understand the best use of this. Can someone help me to understand what is the features and benefits of using a temporary table in Oracle (create temporary table temp_table) over an ordinary table (create table temp_table) )

Answer 1

Given below are some points why and when we should temporary table :-

1)Temporary tables are created for storing the data in a tabular form, for easy retrieval of it when needed, with in that particular session.

2)It also add a security purpose of keeping the data available only for that particular session.

3) When a code goes long and a lot of cursors are opened it better to put the data in a temporary table so that it can be easily fetched at the time needed.

Answer 2

From the concepts guide:

A temporary table definition persists in the same way as a permanent table definition, but the data exists only for the duration of a transaction or session. Temporary tables are useful in applications where a result set must be held temporarily, perhaps because the result is constructed by running multiple operations.

And:

Data in a temporary table is private to the session, which means that each session can only see and modify its own data.

So one aspect is that the data is private to your session. Which is also true of uncommitted data in a permanent table, but with a temporary table the data can persist and yet stay private across a commit (based on the on commit clause on creation).

Another aspect is that they use temporary segments, which means you generate much less redo and undo overhead using a temporary table than you would if you put the same data temporarily into a permanent table, optionally updated it, and then removed it when you'd finished with it. You also avoid contention and locking issues if more than one session needs its own version of the temporary data.