Creating a Temporary Table Global Temporary Table- Tables and Constraints

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a table that stores data only provisionally. You can specify that the temporary table retain the data for a session or until a transaction commits. Use ON COMMIT PRESERVE ROWS to specify that the data be deleted at the end of the user’s session. In this example, the rows will be retained until the user either explicitly deletes the data or terminates the session:

SQL> create global temporary table analyzed_tables on commit preserve rows as select * from user_tables where lst_analyzed > sysdate – 1;

Specify ON COMMIT DELETE ROWS to indicate that the data should be deleted at the end of the transaction. The following example creates a temporary table named
TEMP_OUTPUT and specifies that records should be deleted at the end of each committed transaction:

create global temporary table temp_output( temp_row varchar2(30))
on commit delete rows;

Note If you do not specify a commit method for a global temporary table, then the default is ON COMMIT DELETE ROWS.

You can create a temporary table and grant other users access to it. However, a session can only view the data that it inserts into a table. In other words, if two sessions are using the same temporary table, a session cannot select any data inserted into the temporary table by a different session.

A global temporary table is useful for applications that need to briefly store data in a table structure. After you create a temporary table, it exists until you drop it. In other words, the definition of the temporary table is “permanent”—it is the data that is short-lived (in this sense, the term temporary table can be misleading).
You can view whether a table is temporary by querying the TEMPORARY column of DBA/ALL/USER_TABLES:

SQL> select table_name, temporary from user_tables;

Temporary tables are designated with a Y in the TEMPORARY column. Regular tables contain an N in the TEMPORARY column.
When you create records in a temporary table, space is allocated in your default temporary tablespace. You can verify this by running the following SQL:

SQL> select username, contents, segtype from v$sort_usage;

If you are working with a large number of rows and need better performance for selectively retrieving rows, you may want to consider creating an index on the appropriate columns in your temporary table:

SQL> create index temp_index on temp_output(temp_row);

Use the DROP TABLE command to drop a temporary table:

SQL> drop table temp_output;