Temporary Tables

Is it possible to create temporary tables in Oracle? If so how can one create the same? Can someone provide the syntax for doing this?

In Oracle 8i, the CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. The table definition itself is not temporary.

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The on commit delete rows clause indicates that the data should be deleted at the end of the transaction. Create global temporary table my_temp_table ( column1 number, column2 number ) on commit delete rows; in contrast, the on commit preserve rows clause indicates that rows should be preserved until the end of the session. Create global temporary table my_temp_table ( column1 number, column2 number ) on commit preserve rows;

Both of your explanation was simply superb for me to get a detailed understanding. With this understanding I even tried creating one Temporary Table.

Hai,

The information given about Temporary tables are very useful. I tried with Session-specific. But i dont know how to create transaction-specific Temporary tables. could anybody explain transaction-specific concept with a simple example?

-----
Bhuvana.

Hi Bhuvana,

Creation of Transaction-specific Temporary tables is similar to session specific temporary tables creation but the difference lies in the "On Commit" parameter specified.
When data is inserted into the temporary table the data persists either at the session level or transaction level based "delete rows" or "preserve rows" of "On Commit" parameter.
Example for Transaction level Temporary table,
CREATE GLOBAL TEMPORARY TABLE temptab
( column1 number, column2 varchar(5))
on commit delete rows;

Hope its clear.

*** Innila ***