Notes on private temporary tables

By Brian Fitzgerald

New in 18c, private temporary tables are temporary database objects that are dropped at the end of a transaction or session. Private temporary tables are stored in memory and each one is visible only to the session that created it.

Here are a few findings on private temporary tables (PTT).

Basic operation

Create, insert, and select.

create private temporary table ora$ptt_a
(
 n number
) on commit preserve definition;
insert into ora$ptt_a ( n ) values ( 0 );
select n 
from ora$ptt_a;

Table created.
1 row(s) inserted.

N
0

A PTT cannot be partitioned or index-organized.

Using “on commit drop definition”

Commit drops the PTT if the PTT is declared “on commit drop definition”.

create private temporary table ora$ptt_a
(
n number
) on commit drop definition;
insert into ora$ptt_a ( n ) values ( 0 );
commit;
select n 
from ora$ptt_a;

Table created.
1 row(s) inserted.
Statement processed.
ORA-00942: table or view does not exist

The same applies to rollback:

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit drop definition;
rollback;
select n from ora$ptt_a;
Table dropped.
Table created.
Statement processed.
ORA-00942: table or view does not exist

No commit on create

Creating a PTT does not itself issue a commit, as it would with a conventional table. This fact leads to the primary use case of PTTs:

  • “When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session”

In other words, a PTT can be used as a driving table which you populate once, and then use for multiple queries.

Rollback

Rollback rolls back the insert, but not the create table.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;
insert into ora$ptt_a ( n ) values ( 4 );
rollback;
select n from ora$ptt_a;

Table dropped.
Table created.
1 row(s) inserted.
Statement processed.
no data found

Alter table

Alter table add column fails with an erroneous ORA-00942:

alter table ora$ptt_a add m number;
ORA-00942: table or view does not exist

ORA-00942 can be taken to mean that Oracle searched for, and did not find a conventional table, and is indicative of a bug.

Views

Views with info about PTTs are:

USER_PRIVATE_TEMP_TABLES

DBA_PRIVATE_TEMP_TABLES

There is no ALL_PRIVATE_TEMP_TABLES.

select * from USER_PRIVATE_TEMP_TABLES;

(results transposed)

SID 2284
SERIAL# 40199
OWNER SQL_RADNMXBEQPEYTXKXEYBLDVRPC
TABLE_NAME ORA$PTT_B
TABLESPACE_NAME TEMP
DURATION SESSION
NUM_ROWS 0
BLOCKS 0
AVG_ROW_LEN 0
LAST_ANALYZED 2/18/2018 23:01
TXN_ID 0
SAVE_POINT_NUM 0

sid, serial# refer to the session that created the PTT.

Parallel

The parallel create option succeeds:

drop table ora$ptt_a;
create private temporary table ora$ptt_a
 (
 d date
 )
 on commit preserve definition 
 parallel 8;

Table dropped. 

Table created.

However, I find the parallel degree nowhere in the catalog.

Altering the parallel degree fails with an erroneous ORA-00942:

alter table ora$ptt_a parallel 4;
ORA-00942: table or view does not exist 

No primary key

You cannot declare a primary key

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number primary key
) 
on commit drop definition;

ORA-14451: unsupported feature with temporary table

You cannot create indexes, defaults, or not null constraints.

Dropping

You can explicitly drop a PTT.

drop table ora$ptt_a;

Table dropped.

Prefix

You can prefix the table name with the owner when referring to it.

drop table SQL_ZZOZIKMDVVNDRUUEJJIJXJMKR.ora$ptt_a;
create private temporary table SQL_ZZOZIKMDVVNDRUUEJJIJXJMKR.ora$ptt_a
on commit preserve definition
as select level n
from dual
connect by level <= 2;

rowid

You can query rowid. dbms_rowid.rowid_object returns a number that is unique per PTT and not in user_objects.

create private temporary table ora$ptt_a
as
select level n from dual connect by level <= 3;

create private temporary table ora$ptt_b
as
select level n from dual connect by level <= 3;
select rowid, n, dbms_rowid.rowid_object(rowid) from ora$ptt_a;

select rowid, n, dbms_rowid.rowid_object(rowid) from ora$ptt_b;
select max(object_id) from user_objects;
Table created.
Table created.
ROWID N DBMS_ROWID.ROWID_OBJECT(ROWID)
AATFyHAABAADFyIAAA 1 5004423
AATFyHAABAADFyIAAB 2 5004423
AATFyHAABAADFyIAAC 3 5004423
ROWID N DBMS_ROWID.ROWID_OBJECT(ROWID)
AATF4HAABAADF4IAAA 1 5004807
AATF4HAABAADF4IAAB 2 5004807
AATF4HAABAADF4IAAC 3 5004807
MAX(OBJECT_ID)
129337

Flashback query

Flashback queries are not allowed on temporary tables. The expected message is:

ORA-30051: VERSIONS clause not allowed here

However flashback query on PTTs fail with an erroneous ORA-00942:

select n from ora$ptt_a
versions between scn 10717996 and 10720679;
ORA-00942: table or view does not exist

Grant

You cannot grant access to a PTT. Grant fails with an erroneous ORA-00942.

grant select on ora$ptt_a to system;
ORA-00942: table or view does not exist

Truncate

You can truncate a PTT. Truncating a PTT does not issue a COMMIT, as it would with a conventional table.

create global temporary table gtt_a
(
n number 
)
on commit delete rows;
create private temporary table ora$ptt_a
(
n number
)
on commit drop definition;
insert into ora$ptt_a
select level from dual 
connect by level <= 10000;
insert into gtt_a
select level from dual 
connect by level < 10000;
select count(*)nptt from ora$ptt_a;
truncate table ORA$PTT_A;
select count(*)nptt from ora$ptt_a;
select count(*)ngtt from gtt_a;
commit;
select count(*)ngtt from gtt_a;

Table created.
Table created.
10000 row(s) inserted.
9999 row(s) inserted.
NPTT
10000
Table truncated.
NPTT
0
NGTT
9999
Statement processed.
NGTT
0

Gather stats

You cannot gather stats on a private temporary table.

begin
dbms_stats.gather_table_stats(null,'ora$ptt_a');
end;
/

ORA-20000: Unable to analyze TABLE "SQL_RADNMXBEQPEYTXKXEYBLDVRPC"."ORA$PTT_A", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 39094
ORA-06512: at "SYS.DBMS_STATS", line 38371
ORA-06512: at "SYS.DBMS_STATS", line 38530
ORA-06512: at "SYS.DBMS_STATS", line 39076
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

You cannot analyze a PTT

analyze table ora$ptt_a estimate statistics;

ORA-00942: table or view does not exist 

PTT statistics normally show 0 rows; however, PTT statistics get populated in the case of create table as select.

create private temporary table ora$ptt_a
as select level n
from dual
connect by level <= 10000;
select num_rows, blocks, avg_row_len
from user_private_temp_tables;
Table created
NUM_ROWS BLOCKS AVG_ROW_LEN
10000 16 4

Metadata

PTTs do not appear in USER_TABLES, USER_SEGMENTS, or USER_OBJECTS.

PTTs do not have an object_id.

No PTT column metadata has been found (so far). However, I would check x$ tables.

Multiple sessions

Multiple sessions by the same user can create a PTT having the same name. The definition and the data are visible only to the creating session.

Name clash

You cannot create a conventional table, or any other object, beginning with “ORA$PTT_”.

create procedure ora$ptt_p is begin null; end;
/

ORA-32463: cannot create an object with a name 
matching private temporary table prefix 

The PTT prefix can be changed using initialization parameter PRIVATE_TEMP_TABLE_PREFIX, but it cannot be modified at the session level.

alter session set PRIVATE_TEMP_TABLE_PREFIX = 'PRIV$TMP_';

ORA-02096: specified initialization parameter is not modifiable 
with this option

To change the PTT prefix at the instance level, issue, for example:

alter system set PRIVATE_TEMP_TABLE_PREFIX = 'PRIV$TMP_' deferred;

Current sessions are unaffected. Future connections will catch the new setting.

PL/SQL

You can use a PTT in an anonymous PL/SQL block

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;
insert into ora$ptt_a ( n ) values ( 7 );
declare
 l_num number;
begin
 select n into l_num from ora$ptt_a;
 dbms_output.put_line('l_num='||l_num);
end;
/
Table dropped.
Table created.
1 row(s) inserted.
l_num=7

A PTT column cannot be used in a type declaration.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;
declare
 l_num ora$ptt_a.n%type;
begin
 null;
end;
/
Table dropped.
Table created.
ORA-06550: line 2, column 9:
PLS-00201: identifier 'ORA$PTT_A.N' must be declared

You cannot create a procedure that uses a PTT in static PL/SQL.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;

create or replace procedure pr
as
begin
 insert into ora$ptt_a ( n ) values ( 0 );
end;
/
select line, position, text from user_errors where name = 'PR';

Table dropped. 

Table created.
Error at line: 12
LINE POSITION TEXT
4 14 PL/SQL: ORA-14451: unsupported feature with temporary table
4 2 PL/SQL: SQL Statement ignored

You can write an anonymous PL/SQL block that declares a procedure that uses a PTT.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
(
n number
)
on commit preserve definition;

declare
procedure pr
as
begin
 insert into ora$ptt_a ( n ) values ( 5 );
end pr;
begin
 pr;
end;
/
select n from ora$ptt_a;
Table dropped.
Table created.
1 row(s) inserted.

N
5

Notice the feedback on the insert. I have not seen such feedback in PL/SQL before. This demo was run on Oracle Live SQL.

You can create a package that creates and uses a PTT using dynamic SQL.

drop table ora$ptt_a;
create or replace package ptt_pkg
is
 procedure crptt;
 procedure insptt;
 function pttval
 return number;
end ptt_pkg;
/
create or replace package body ptt_pkg
is
 procedure crptt
 is
 begin
 execute immediate
 q'{create private temporary table ora$ptt_a
 (
 n number
 )
 on commit preserve definition}';
 end crptt;

procedure insptt
 is
 begin
 execute immediate
 q'{insert into ora$ptt_a ( n ) values ( 3 )}';
 end insptt;

function pttval
 return number
 is
 l_num number;
 begin
 execute immediate
 q'{select n
 from ora$ptt_a}' into l_num;
 return l_num;
 end pttval;
end ptt_pkg;
/

declare
 l_num number;
begin
 ptt_pkg.crptt;
 ptt_pkg.insptt;
 l_num := ptt_pkg.pttval;
 dbms_output.put_line('in package l_num='||l_num);
end;
/

Table dropped.
Package created.
Package Body created.
in package l_num=3

An anonymous PL/SQL block will not compile if it refers to an object that does not exist yet. This will not work:

declare
l_num number;
begin
ptt_pkg.crptt;
ptt_pkg.insptt;
select n into l_num
from ora$ptt_a;
end;
/

ORA-06550: line 7, column 6: 
PL/SQL: ORA-00942: table or view does not exist

In-memory

Dan Morgan reports “ORA-14451: unsupported feature with temporary table” in a PDB with inmemory_size set. Please refer to the blog feedback.

Explain plan

You can run explain plan on a statement that uses  PTT.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
 (
 n number
 )
 on commit preserve definition;
explain plan for
select n
from ora$ptt_a;
select * from table ( dbms_xplan.display );
Table dropped.
Table created.
Statement processed.

PLAN_TABLE_OUTPUT
Plan hash value: 2125934360

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS STORAGE FULL| ORA$PTT_A | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Parallel plan

The optimizer can create a parallel plan on a PTT.

drop table ora$ptt_a;
create private temporary table ora$ptt_a
 (
 n number
 )
 on commit preserve definition
 parallel 8;
explain plan for
select n
from ora$ptt_a;
select * from table ( dbms_xplan.display );
Table dropped.
Table created.
Statement processed.

PLAN_TABLE_OUTPUT
Plan hash value: 2895541530

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS STORAGE FULL| ORA$PTT_A | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------

Note
-----
 - dynamic statistics used: dynamic sampling (level=2)
 - Degree of Parallelism is 8 because of table property

Troubleshooting

Query troubleshooting could be impeded by lack of information about the design of the PTT.

Prerequisites

The create table privilege is required to create a PTT. Granting create table permits creating any type of table, not just PTTs. The feature is clearly intended for use in application code at run time. The DBA is faced with a choice: Grant create table to the application run time user, or deny the use of private temporary tables.

Caution on new features

The PTT is a new 18c feature. Users should exercise care and be alert to bugs in PTTs. Bugs tend to appear when multiple lightly used features are combined. Beware of performance, internal error, and corruption bugs. Also, optimization can lead to results errors. Use PTTs when the potential business value outweighs the risk and added testing cost. Exercise conservative practices when using PTTs.

Conclusion

PTTs are a new Oracle Database 18c feature. Before using PTTs it’s a good idea to be aware of the restrictions.

5 thoughts on “Notes on private temporary tables

  1. Have you seen this yet?

    SQL> SELECT * FROM v$version;

    BANNER BANNER_FULL BANNER_LEGACY CON_ID
    —————————————- —————————————- —————————————- ———-
    Oracle Database 18c EE High Perf Release Oracle Database 18c EE High Perf Release Oracle Database 18c EE High Perf Release 0
    18.0.0.0.0 – Production 18.0.0.0.0 – Production 18.0.0.0.0 – Production

    SQL> sho con_id

    CON_ID
    ——————————
    4

    SQL> show con_name

    CON_NAME
    ——————————
    PDB1

    SQL> ALTER SYSTEM SET inmemory_size=400M;

    System altered.

    SQL> col value format 9999999999
    SQL> SELECT * FROM v$sga;

    NAME VALUE CON_ID
    —————————— ———– ———-
    Fixed Size 8899800 0
    Variable Size 620756992 0
    Database Buffers 1644167168 0
    Redo Buffers 74985472 0
    In-Memory Area 419430400 0

    SQL> show parameter compatible

    NAME TYPE VALUE
    ———————————— ———– ——————————
    compatible string 18.0.0
    noncdb_compatible boolean FALSE

    SQL> create private temporary table ora$ptt_a
    (
    n number
    ) on commit preserve definition; 2 3 4
    create private temporary table ora$ptt_a
    *
    ERROR at line 1:
    ORA-14451: unsupported feature with temporary table

    This is 18c deployed in the Oracle Public Cloud.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s