Global Temporary Tables(Basics)
What are
global temporary table?
The idea of a global temporary table is that the
definition(attributes) exists and can be seen by all, but data is private for
each session. You can also configure if the data is cleaned upon commit or only
when the session ends.
 |
| Multi User Accessing the multi session handled by Global temporary table |
How
do they differ from regular
Oracle tables?
Only the session that INSERTED data into the table can see that data. This means several sessions
(perhaps all with the same user) can work at the same time, and not interfere
with each other. (Of course, that's just a positive way of saying they will not
communicate with each other.)
Rows in a global temporary table are automatically deleted when the transaction ends
(if the table was
created with "ON COMMIT *DELETE* ROWS", which is the default) or when
the session ends (if the table was created with "ON COMMIT *PRESERVE*
ROWS").
Storage
of Data
Global temporary table data
is stored in the temporary tablespace(where no redowill be generated on the DML operations).Saves Server HDD
and Processing time.
 |
| Storage of Global Temp Table |
Technical Specifications(Create
Table options)
On commit preserve Rows(Saves rows till
session ends).
On commit Delete Rows (Delete rows after commit).
Miscellaneous Features
If the TRUNCATE statement is issued against a temporary table, only
the session specific data is truncated. There is no affect on the data of other
sessions.
•Data in temporary tables is automatically deleted at the end of the
database session, even if it ends abnormally.
•Views can be created against temporary tables and combinations of
temporary and permanent tables.
•Export and Import utilities can be used to transfer the table definitions,
but no data rows are processed.
•Statistics on temporary tables are common to all sessions. Oracle
12c allows session specific statistics.
