All about oracle Global Temporary Tables

 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.