Global temporary tables are helpful when storing provisional data that is only needed for a transaction or session duration. Unlike regular tables, global temporary tables are automatically dropped when the session or transaction ends. So, they do not need to be explicitly dropped like normal tables. However, global temporary tables are only visible to the current session, so they cannot be accessed by other sessions or users.
In Oracle, a global temporary table is a special type of table created using the “create global temporary table” statement. This statement is similar to the regular “create table” statement but includes the keyword “global temporary” to specify that the table is a global temporary table.
The syntax of the “CREATE GLOBAL TEMPORARY TABLE” statement is as follows:
column1 datatype [NULL | NOT NULL],
column2 datatype [NULL | NOT NULL],
...
) [ON COMMIT {DELETE | PRESERVE} ROWS];
In this syntax, table_name is the name of the global temporary table you want to create. column1, column2, etc., are the names and data types of the columns in the table.
The ON COMMIT clause specifies whether the rows in the table will be deleted or preserved when the current transaction is committed. The database engine will use the ON COMMIT DELETE ROWS option if the ON COMMIT clause is not defined.
As mentioned, keep in mind that the data in a temporary table is private. This means that no other sessions other than which created the table can access it.
Creating Global Temporary Table Example
Let us discover some practical examples of using the create temporary table statement in Oracle databases.
Consider the example query shown below:
product_id NUMBER(10) NOT NULL,
sale_date DATE NOT NULL,
sale_amount NUMBER(10,2) NOT NULL
) ON COMMIT DELETE ROWS;
In the example above, we create a temporary table using the ON COMMIT DELETE ROWS option.
We can then insert some sample data as:
INSERT INTO TEMP_SALES (product_id, sale_date, sale_amount) VALUES (2, DATE '2022-10-02', 500);
INSERT INTO TEMP_SALES (product_id, sale_date, sale_amount) VALUES (3, DATE '2022-10-03', 130);
After that, you can commit the transaction as:
After committing, the database engine will truncate all the data in the temporary table as specified in the commit clause.
Example 2
The following example shows how to create a table that preserves rows on commit:
product_id NUMBER(10) NOT NULL,
sale_date DATE NOT NULL,
sale_amount NUMBER(10,2) NOT NULL
) ON COMMIT PRESERVE ROWS ;
We can then add sample rows and commit as shown:
INSERT INTO TEMP_SALES (product_id, sale_date, sale_amount) VALUES (2, DATE '2022-10-02', 500);
INSERT INTO TEMP_SALES (product_id, sale_date, sale_amount) VALUES (3, DATE '2022-10-03', 130);
COMMIT;
In this case, the data should be preserved after the commit operation, as shown by the select statement below:
Output:
Conclusion
The “create temporary table” statement is a powerful tool for creating temporary tables in Oracle. Temporary tables are useful for storing temporary data only needed for a transaction or session. The “create temporary table” statement allows you to define the structure and columns of the temporary table and specify how the rows will be handled when the transaction is committed. As a result, using temporary tables can improve the performance and efficiency of your queries and applications and reduce the amount of permanent storage space required in your database.
