How to create a private temporary table from Oracle 18c and onwards

The default is to throw away the data after a transaction ends with commit or rollback:

create private temporary table ora$ptt_mytemptab
on commit drop definition;

You could also let the data be visible for the entire duration of the session:

create private temporary table ora$ptt_mytemptab
on commit preserve definition;

The notation ora$ptt is derived from the parameter private_temp_table_prefix:

SQL>show parameter private_temp_table_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
private_temp_table_prefix            string      ORA$PTT_

It must be included in the create statement of the PTT. You can find information about your PTTs in the dictionary:

set lines 200
col table_name format a20
col tablespace_name format a20
col duration format a20
col num_rows format 99999999
SELECT sid, serial#, table_name, tablespace_name, duration, num_rows
FROM   user_private_temp_tables;

In my case the output is:

      SID    SERIAL# TABLE_NAME           TABLESPACE_NAME      DURATION              NUM_ROWS
---------- ---------- -------------------- -------------------- -------------------- ---------
       280      12720 ORA$PTT_MYTEMPTAB   TEMP                 SESSION                    107

The column "duration" will show whether it is preserving data for the session or the transaction.

Read more about PTTs in the Oracle 18c documentation

How to work around ORA-03206 when running the RCU (Repository Creation Utility) used by ODI

During installation you may encounter the error

ORA-03206: maximum file size of (13107200) blocks in AUTOEXTEND clause is out of range

In my case, the RCU attempted to create a temporary tablespace with autoextend of 100G. This is too much for a smallfile tablespace, and the error is thrown.
I checked the database creation scripts, and it is indeed created with smallfile as the default tablespace type.

To solve the problem, alter your database default setting:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

and retry the operation.

Documentation for Oracle 12.2 can be found here

How to create a global temporary table

An example of a Global Temporary Table (GTT) would be:

CREATE GLOBAL TEMPORARY TABLE INCOMING_DATA (
  ROW_ID                      NUMBER(10) NOT NULL,
  SSN                         NUMBER(11),
  ENTRY_DATE                  DATE NOT NULL,
  HANDLED_BY                  VARCHAR2(30 BYTE) NOT NULL,
  CONTENT_CODE                VARCHAR2(4 BYTE),
  MESSAGE                     CLOB
 )
ON COMMIT DELETE ROWS
;

ON COMMIT DELETE ROWS indicates that Oracle will purge the rows after an ended transaction (after a COMMIT, ROLLBACK or an implicit COMMIT).
Instead of ON COMMIT DELETE ROWS you could also use ON DELETE PRESERVE ROWS, which will remove the rows at the end of the user's session.

You can add indexes, triggers and views on a GTT. You can even truncate a temporary table, and it will only affect the curren session's rows, leaving other users' rows intact.


With Oracle 12c came the ability for temporary tables to store its UNDO segments in temporary tablespaces, rather than conventional tablespaces, thus removing the need to generate REDO.

To enable this for your session, use:

ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

before creating the GTT.

Sources:

Oracle-Base: article about GTT and another one explaining the 12c new feature "temporary undo"

Oracle Documentation: Overview of GTT
Check my note on private temporary tables, available from Oracle 18c.