Oracle Global Temporary Table Example

They are special kind of tables in Oracle which have structure and definition same as other usual Oracle tables but where data is not persistent.

Data is only session specific or transaction specific. This means that users can insert data into the table simultaneously but they will never see each others data.

Syntax:

CREATE GLOBAL TEMPORARY TABLE SCHEMA_NAME.TABLE_NAME
(
COL1        DATATYPE,
COL2        DATATYPE,
.
.
.
COLNn        DATATYPE
)
ON COMMIT DELETE ROWS
NOCACHE;


There are two options available for ON COMMIT clause

DELETE ROWS: truncate data after each commit.

PRESERVE ROWS: truncate table when session ends.

Example of usage of GLOBAL TEMPORARY TABLE:

There can be several scenarios where using a PL/SQL table is not suitable or may be more complicated than using a temporary table.

For example suppose you have some data coming into a remote database at regular intervals. You want to transfer those data into your local database but only after processing. Once you process your data you may want to join these data with other tables in your local database and then based on the results you will insert them into your local database.

In this case you can create a global temporary table and perform the steps as follows:

1. Get your remote data into a cursor using database link

2. Processes your data based on your requirement (such as filtering/modifying).

3. Insert the changed data into a global temporary table.

4. Join the global temporary table with other local oracle tables based on your requirement.

5. Put the resultant data into its final destination table in your local database or to another remote database.

Limitations:

However there are some limitations in using global temporary table. They can not have any foreign keys; they can not have any storage clause and they can not be partitioned.



Loading