Temporary Table Redo- Tables and Constraints

No redo data are generated for changes to blocks of a global temporary table. However, rollback (undo) data is generated for a transaction against a temporary table. Because the rollback data generates a redo, some redo data are associated with a transaction for a temporary table. You can verify this by turning on statistics tracing and viewing the redo size as you insert records into a temporary table:

SQL> set autotrace on

Next, insert a few records into the temporary table:

SQL> insert into temp_output values(1);

Here is a snippet of the output (showing only the redo size):

140 redo size

The redo load is less for temporary tables than normal tables because the redo generated is associated only with the rollback (undo) data for a temporary table transaction.

Additionally, the undo for temporary objects is stored in the temporary tablespace, not the undo tablespace.

Private Temporary Tables

Oracle 18c introduced private temporary tables, which are a memory-based temporary table that is used for the session or transaction and then dropped.

This is different from the global temporary table, as the metadata of the table is not permanent but is the same as the data, and in this case the whole table is for the session and private.

There is a PRIVATE_TEMP_TABLE_PREFIX parameter for defining the prefix of the name for the table, with a default of ORA$PTT_. Also, it’s important to know because how the temp table is to be used, it is expected that you are a user schema, and the private temporary table creation will fail if you are SYS or SYSTEM users.

You need to use the prefix to create the table:

SQL> create private temporary table ora$ppt_work_temp_table ( id number,

name_desc    varchar2(80), valid_values     number)

on commit preserve definition;

The ON COMMIT DROP DEFINITION clause is the default, which means the table will be dropped when the transaction is finished.

It does make sense that to use the private temporary table as dynamic in PL/SQL, where if you need an existing table in PL/SQL, the object will need to be referenced to compile, and it would be useful to use a global temporary table.

In PL/SQL:

> begin

execute immediate ‘create private temporary table ora$ptt_work_temp_table (id number, name_desc varchar2(80)) on commit drop definition’;

…

execute immediate ‘insert into ora$ptt_work_temp_table values(1,’Testing’)’;

… end; /

Since private temporary tables are memory-based, there is no metadata captured in the data dictionary, and the data cannot be accessed by database links or materialized views. While in the transaction or session, you will be able to use the table DBA/USER_ PRIVATE_TEMP_TABLES to see the details. Already discussed was that the PL/SQL would need to include it in an execute immediate statement as with dynamic SQL.