Private Temporary Tables
Oracle 18c introduced 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 SQL Server developers will regularly create a temporary table to do some work and drop it. In Oracle a Global Temporary Table (GTT) is a permanent metadata object that holds rows in temporary segments on a transaction-specfic or session-specific basis. It is not considered normal to create and drop GTTs on the fly. With the introduction of private temporary tables, Oracle has an option similar to that seen in other engines, where the table object itself is temporary, not just the data.
Create a Private Temporay Table
- There are 2 options:
- one options where the table is removed automatically after a transaction commit (or rollback). This is the default option
- one option where the table is kept for the length of the session rather than the transaction. This is indicated by the optional create table clause [ON COMMIT PRESERVE]
- The PRIVATE_TEMP_TABLE_PREFIX initialisation parameter, which defaults to "ORA$PTT_", defines the prefix that must be used in the name when creating the private temporary table.