Oracle Database 18c introduces the concept of a private temporary table—a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup. In the previous releases, you can create a Global Temporary Table (GTT), which is a permanent metadata object that holds rows in temporary segments on a transaction-specific or session-specific basis. Normally, you don’t create and drop GTTs on the fly. With the introduction of private temporary tables, Oracle has a similar option available in other engines, where the table object itself is temporary, not just the data.

The PRIVATE_TEMP_TABLE_PREFIX initialization parameter, which defaults to ORA$PTT_, defines the prefix that you must use in the name when creating the private temporary table. If you try to create a private temporary table without using the correct prefix in the name, an error results.

The default ON COMMIT DROP DEFINITION clause drops the table at the end of the transaction or at the end of the session.

Use the following syntax to create a private temporary table:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp12 (
id NUMBER,description VARCHAR2(10)
)  ON COMMIT DROP DEFINITION;

Share this post
Recommended Posts

Leave a Comment

Start typing and press Enter to search