Oracle 18c New Feature Private Temporary Tables

Active Data Guard databases are now no longer just ‘read-only’ databases – they have now become ‘read-mostly’ databases which is primarily used for reporting purposes but also allows to some extent DML activity as well.

One of the new features in Oracle 18c is Private Temporary Tables.

Private temporary tables differ from Global Temporary tables in some ways. They are not stored on disk but only memory and are only visible to the session which creates them. The name of the table must be prefixed with the string ‘ORA$PTT‘

They are temporary database objects which are dropped either at the end of the transaction or end of the session. Different sessions of the same user can use the same name for the private temporary table.

These tables can be useful when the application which is predominantly read-only also has a requirement to perform some DML activity like inserting or updating some temporary data in transient tables that are then queried a few times and then dropped at the end of either a transaction or session.

Let us have a look at this feature.

We connect as user HR to the pluggable database PDB1 – but to the Active Standby read-only database.
 
Session 1 of user HR
 

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_ADG_SESSIONS
  2   (username      varchar2(20), sid number , serial# number)
  3  ON COMMIT PRESERVE DEFINITION;

Table created.

SQL> insert into ORA$PTT_ADG_SESSIONS
 select s.username          i_username, to_char(s.sid)          i_sid, to_char(s.serial#)      i_serial
from 
v$session s, v$process p
where 
s.paddr = p.addr
and 
sid = (select sid from v$mystat where rownum = 1);  

1 row created.

SQL> select * from ORA$PTT_ADG_SESSIONS;

USERNAME		    SID    SERIAL#
-------------------- ---------- ----------
HR			    465      20742

 
Session 2 of user HR
 
Note that the table name is the same – but the data is different.
 

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_ADG_SESSIONS
  2  (username      varchar2(20), sid number , serial# number)
  3  ON COMMIT PRESERVE DEFINITION;

Table created.

SQL> insert into ORA$PTT_ADG_SESSIONS
 select s.username          i_username, to_char(s.sid)          i_sid, to_char(s.serial#)      i_serial
from 
v$session s, v$process p
where 
s.paddr = p.addr
and 
sid = (select sid from v$mystat where rownum = 1);  2    3    4    5    6    7    8  

1 row created.

SQL>  select * from ORA$PTT_ADG_SESSIONS;

USERNAME		    SID    SERIAL#
-------------------- ---------- ----------
HR			    472      46742

SQL> 

 
Reconnect as user HR
 

SQL> conn hr/hr@pdb1
Connected.

SQL> select * from ORA$PTT_ADG_SESSIONS;
select * from ORA$PTT_ADG_SESSIONS
              *
ERROR at line 1:
ORA-00942: table or view does not exist

 
Private Temporary Table with ON COMMIT DROP DEFINITION
 

SQL>  CREATE PRIVATE TEMPORARY TABLE ORA$PTT_ADG_SESSIONS
  2   (username      varchar2(20), sid number , serial# number)
  3  ON COMMIT DROP DEFINITION;

Table created.

SQL>  insert into ORA$PTT_ADG_SESSIONS
 select s.username          i_username, to_char(s.sid)          i_sid, to_char(s.serial#)      i_serial
from 
v$session s, v$process p
where 
s.paddr = p.addr
and 
sid = (select sid from v$mystat where rownum = 1);   

1 row created.

SQL> select * from ORA$PTT_ADG_SESSIONS;

USERNAME		    SID    SERIAL#
-------------------- ---------- ----------
HR			    465      20742

SQL> update ORA$PTT_ADG_SESSIONS set USERNAME='NOBODY';

1 row updated.

SQL> select * from ORA$PTT_ADG_SESSIONS;

USERNAME		    SID    SERIAL#
-------------------- ---------- ----------
NOBODY			    465      20742

SQL> commit;

Commit complete.

SQL>  select * from ORA$PTT_ADG_SESSIONS;
 select * from ORA$PTT_ADG_SESSIONS
               *
ERROR at line 1:
ORA-00942: table or view does not exist