Global Temporary Table (GTT) in Oracle SQL

Definition: A Global Temporary Table (GTT) is a type of table in Oracle that stores data temporarily for the duration of a session or a transaction. The structure of the table is persistent, but the data contained within it is transient and can be session-specific or transaction-specific.

Characteristics of Global Temporary Tables:

The table structure (columns and data types) is defined just like a regular table.

Data is only accessible to the session that inserted it.

Data can either persist for the duration of the session or until the end of the transaction, based on how the table is defined.

GTTs do not consume space in the database for their data unless rows are inserted.

Example of Creating and Using a Global Temporary Table

Creating a Global Temporary Table:

CREATE GLOBAL TEMPORARY TABLE temp_employee_data (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_salary NUMBER
) ON COMMIT DELETE ROWS; -- Data will be deleted at the end of the transaction

In this example:

We create a table named temp_employee_data with three columns: emp_id, emp_name, and emp_salary.

The clause ON COMMIT DELETE ROWS means that the data will be deleted after each transaction (i.e., after every COMMIT).

Inserting Data into the Global Temporary Table:

INSERT INTO temp_employee_data (emp_id, emp_name, emp_salary)
VALUES (101, 'Alice', 60000);

INSERT INTO temp_employee_data (emp_id, emp_name, emp_salary)
VALUES (102, 'Bob', 70000);

Querying Data from the Global Temporary Table:

SELECT * FROM temp_employee_data;

This will return:

EMP_ID | EMP_NAME | EMP_SALARY

101 | Alice | 60000
102 | Bob | 70000

Committing the Transaction:

COMMIT;

After executing the COMMIT statement, the data in temp_employee_data will be deleted, since we specified ON COMMIT DELETE ROWS.

Verifying the Data Deletion:

SELECT * FROM temp_employee_data;

This will return no rows because the data was deleted after the commit.

Example with Data Persisting for the Session

If you want the data to persist for the duration of the session instead, you can define the table as follows:

CREATE GLOBAL TEMPORARY TABLE temp_employee_data (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_salary NUMBER
) ON COMMIT PRESERVE ROWS; -- Data will persist until the session ends

In this case, the data will remain in the table until the session is closed, even if you commit the transaction.

Summary

Global Temporary Tables are useful for storing intermediate results temporarily without affecting the permanent tables in your database. They provide a powerful way to manage session-specific data in Oracle SQL applications.