SQL Global Temporary Table

Global Temporary Table

Global Temporary Table:-The global temporary table is a table that can be seen by all, but data is private for each session.
                        You can also configure for data clean-up on commit or on only when the session ends.
                        Multi User Accessing the multi session handled by Global temporary table
                        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.
Note:-Global temporary tables are an alternative to using the WITH clause to materialize intermediate query results.                       
   
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 for GTT :-Global temporary table data is stored in the temporary tablespace .Saves Server HDD and Processing time.

Create Table options options for GTT:- 1) On commit preserve Rows  (Saves rows till session ends).
                                       2) On commit Delete Rows    (Delete rows after commit).


Features :-
1) 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.
2) Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
3) Views can be created against temporary tables and combinations of temporary and permanent tables.
4) Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
5) Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.

Query to display all global temporary tables:- select table_name from all_tables where temporary = 'Y';

Creation of GTT:-
Syntax:- create global temporary table as ;
Example:-
1) create global temporary table emp_details on commit preserve rows as select *  from emp;
2) create global temporary table student_details (name varchar2(10),dob date,course varchar2(20))
   on commit preserve rows;

Drop a GTT:- drop table student_details;
Note:- If it is in used by any session system will not allowed to drop it. Then need to truncate first then drop.