Difference between revisions of "Private Temporary Tables"

From Robs_Wiki
Jump to: navigation, search
(Introduction)
(Create a Private Temporay Table)
Line 9: Line 9:
 
** 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]
 
** 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.
 
* 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.
 +
<pre>
 +
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
 +
  id          NUMBER,
 +
  description  VARCHAR2(20)
 +
)
 +
ON COMMIT DROP DEFINITION;
 +
</pre>
 +
<pre>
 +
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
 +
  id          NUMBER,
 +
  description  VARCHAR2(20)
 +
)
 +
ON COMMIT DROP DEFINITION;
 +
</pre>

Revision as of 14:04, 13 February 2020

Introduction

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.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;