When to use Global Temporary Table?

Some of the processes needs a temporary table as a storage for private session or transaction specific content. Such a table can be created with one of two options. Rows are private thus not available from outside of the session.

On Commit preserve Rows?

This is a global temporary table with session specific content. The data resides in GTT , thus are not deleted in the end of transaction on commit.

For example:

CREATE GLOBAL TEMPORARY TABLE TMP_MY_DATA(
ID_MY_DATA NUMBER,
MY_DATA_NAME VARCHAR2(100),
MY_DATA_VALUE CLOB
) ON COMMIT PRESERVE ROWS;

On Commit delete Rows?

Creating GTT using on commit delete rows option specifies private data to be deleted in the end of transaction on commit. GTT has transaction specific content.

For example:

CREATE GLOBAL TEMPORARY TABLE TMP_MY_DATA(
ID_MY_DATA NUMBER,
MY_DATA_NAME VARCHAR2(100),
MY_DATA_VALUE CLOB
) ON COMMIT DELETE ROWS;