Global Temporary Tables in Oracle

For more info visit
Global Temporary tables are used for temporary storage of data which has to be cleaned up at the end of some task. Generally, batch programs use temporary tables to store data at some temporary location and then the data is automatically deleted/cleaned at the end of the session or at commit. Data in temporary tables is stored in temp segments in tablespace. Data in the temporary table is session specific, i.e a user saved data in a session cannot be accessed by him/her in other session. A user session is associated with the Global Temporary tables when the data is inserted in a session. Now let's see how the Global Temporary table is created Create Global Temporary table my_tab( name varchar(20), Contact number(10) )On Commit [Delete Rows|Preserve Rows];

We can use two options with Global Temporary tables

a) On Commit Delete Rows: If this option is used, data corresponding to the user session is deleted when transaction completes/is committed. b) On Commit Preserve Rows: When this option is used, data in the Global temporary table persists unless the session is over.

Some features of Global Temporary tables

Note: From here whenever I say table just consider it to be Global Temporary table, as it's very tedious to write it again and again. i) Data in a session cannot be accessed in other session. Though different session of a user are using the same Global Temporary table, data of a session cannot be accessed in other session. In short, Data in the table is session specific. ii) If the table is already created by a session and if we try to create table again in other session, it will give an error. iii) At the end of the session or transaction only session specific data is deleted and not the structure of the table from the user's schema. iv) We can run DDL statements(ALTER TABLE, CREATE INDEX) on the table only when it is not used by any other session. v) Truncating table will only delete the session specific data. vi) The scope of the index created on the table is limited to the session which creates it. vii) Views can be created on the table, or on the table and some other permanent table. viii) We can also associate the table with some trigger but remember we cannot reference the table in the trigger. ix) We can have primary key in the table but we cannot create any referential integrity constraint in the table. At the same time, we cannot access the table  in a referential integrity constraint.  x) Abnormal closing  of the session will truncate the session specific data from the table even if the data was not commited or the session was not over.