Global Temporary Tables - Oracle Tables

Global Temporary Tables, also known as GTT, are a special type of table in Oracle that are created with the ON COMMIT DELETE ROWS clause. GTTs are used to store data temporarily in a database session and are automatically cleared out when the session ends.

Syntax

The syntax for creating a Global Temporary Table in Oracle is as follows:

CREATE GLOBAL TEMPORARY TABLE table_name
(
    column1 data_type,
    column2 data_type,
    ...
)
ON COMMIT DELETE ROWS;

Here, table_name is the name of the temporary table, and column1, column2, etc. represent the columns in the table. The ON COMMIT clause indicates what should happen to the temporary data when the transaction is committed, and the DELETE ROWS clause specifies that the data should be deleted.

Example

Here is an example of creating a Global Temporary Table in Oracle:

CREATE GLOBAL TEMPORARY TABLE sales_temp
(
    id NUMBER,
    date DATE,
    product VARCHAR2(100),
    amount NUMBER
)
ON COMMIT DELETE ROWS;

In this example, we have created a temporary table called sales_temp with four columns: id, date, product, and amount. The ON COMMIT DELETE ROWS clause indicates that the data in the table should be deleted when the transaction is committed.

Output

There is no output when a Global Temporary Table is created. However, when data is inserted into a GTT, it is stored in the table temporarily until the session ends or the data is deleted in a subsequent transaction.

Explanation

Global Temporary Tables in Oracle are created with the ON COMMIT DELETE ROWS clause, which indicates that the data stored in the table will be deleted when the transaction is committed. GTTs are useful for storing temporary data in specific database sessions without permanently affecting the underlying database schema.

Use

Global Temporary Tables are commonly used in applications that generate temporary data that is only needed for a single session or transaction. They provide a way to store data temporarily in a database session without permanently altering the database schema.

Important Points

• Global Temporary Tables are created with the ON COMMIT DELETE ROWS clause.
• GTTs are used to store temporary data in a specific session or transaction.
• The data in a GTT is automatically deleted when the transaction is committed.
• GTTs are useful for storing temporary data in a session without permanently affecting the underlying database schema.

Summary

In summary, Global Temporary Tables are a useful feature in Oracle databases for storing temporary data in a specific session or transaction. They are created with the ON COMMIT DELETE ROWS clause, and the data in the table is automatically deleted when the transaction is committed. GTTs are useful for applications that generate temporary data that is only needed for a single session or transaction.