Global temporary tables

Global temporary tables are types of DB tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.
Note that only table data is session specific, but physically table is available in all sessions.

Syntax
The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS. An alternative is to specify ON COMMIT PRESERVE ROWS.

CREATE GLOBAL TEMPORARY TABLE
(
        [COLUMN DEFINTION]
)       ON COMMIT [DELETE | PRESERVE] ROWS;

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
 
ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Important points about Global temporary tables

1. In Oracle temporary table and global temporary table are synonymous. You cannot create a temp table without the keyword “global”
2. GTT data is private to a session. Although there is a single table definition, each session uses a GTT as if it was privately owned. Truncating data in a GTT will not affect other users sessions.
3. Depending on the table definition, data in a GTT will either be removed or retained after a commit. However it is always removed when the session terminates even if the session ends abnormally.
4. Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the DB session.
5. The table and any associated indexes are stored in the users temporary tablespace. Starting with 11g Oracle introduced the option to specify the temp tablespace for a GTT (but not its indexes).
6. Views can be created against temporary tables and combinations of temporary and permanent tables.
7. Foreign key constraints are not applicable in case 7. of Temporary tables
8. Temporary tables can have triggers associated with them.
9. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
10. Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”
11. Temporary tables cannot be partitioned.
12. If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
13. If your GTT has been defined as ON COMMIT DELETE ROWS, the

                      GATHER_TABLE_STATS call will result in rows being deleted. This is because the
                      GATHER_TABLE_STATS issues an implicit commit.

  14.    If your GTT has been defined as ON COMMIT PRESERVE ROWS, the

                       GATHER_TABLE_STATS will not delete rows in the table.

Simple Example
1 CREATE GLOBAL TEMPORARY TABLE
2 (
3 [COLUMN DEFINTION]
4 ) ON COMMIT [DELETE | PRESERVE] ROWS;
01 -- Create a simple GTT with default options:
02
03 CREATE GLOBAL TEMPORARY TABLE
04 temp_gtt (id NUMBER(20)) ;
05
06 -- Insert some data and do a SELECT
07
08 BEGIN
09 FOR i in 1..10 LOOP
10 INSERT INTO temp_gtt values (i) ;
11 END LOOP ;
12 END ;
13
14 SELECT count(*) FROM temp_gtt;
15
16 -- result:
17
18 COUNT(*)
19 ---------------
20 10
21
22 ‐‐Do a commit and run the query again
23
24 COMMIT;
25
26 SELECT count(*) FROM temp_gtt;
27
28 ‐‐result:
29
30 COUNT(*)
31 -------------
32 0