TIL a new thing about Private Temporary Tables

Many DBAs and Developers are fluent with the use of Global Temporary Tables in the Oracle Database.

However, since 18c we have also had another form of temporary table, namely, Private Temporary Tables. If you’re unfamiliar with them, then here’s a quick video explaining one of the core benefits of them.

There’s a few restrictions on private temporary tables which you can find in the documentation, but in a nutshell

Name has to be prefixed in a certain way (default ORA$PTT_)
No column defaults
No subordinate objects (indexes, mviews etc) on a PTT
No remote access over a dblink
No LOBs (not in the docs, but that will be corrected).

plus a few more

But here is a discovery that was brought to my attention by an attendee at the Yatra conference series in India a couple of weeks ago. There is also a limit on how many PTTs you can concurrently have in a single session. On my local 21c instance, that appears to be 16.


SQL> create private temporary table ORA$PTT_t1 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t2 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t3 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t4 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t5 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t6 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t7 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t8 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t9 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t10 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t11 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t12 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t13 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t14 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t15 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t16 (x int );

Table created.

SQL> create private temporary table ORA$PTT_t17 (x int );
create private temporary table ORA$PTT_t17 (x int )
*
ERROR at line 1:
ORA-32460: maximum number of private temporary tables per session exceeded

To be honest, it does raise the question – Why would anyone need 16 PTTs on the fly at once in a session?, but that’s a different discussion

So keep that in mind if you’re using PTTs. They are designed to be transient things, and that transience also means not having too many floating around at once

Footnote: Well, call me stupid, but I just stumbled across another one of my videos where I make reference to the fact that there is a limit. So looks like I did know about the limit, but just forgot about it 😁

4 responses to “TIL a new thing about Private Temporary Tables”

jammydoughball

March 11, 2024 at 7:41 pm

hi Connor, would PTTs fire DDL triggers when you create them?

1. Connor McDonald
  
  March 11, 2024 at 7:56 pm
  
  Nope … they don’t even impact transactions
  
  SQL> select count(*) from t;
  
  COUNT(*)
  ———-
  14
  
  SQL> delete from t;
  
  14 rows deleted.
  
  SQL> create private temporary table ora$ptt_lll ( y int );
  
  Table created.
  
  SQL> rollback;
  
  Rollback complete.
  
  SQL> select count(*) from t;
  
  COUNT(*)
  ———-
  14
  
Bob

March 19, 2024 at 8:12 am

Hi Conner, where is PTT data stored in SGA and what is the size limit for it?

Thanks Bob

Connor McDonald

March 19, 2024 at 11:58 am

They are stored in the PGA (session specific) and hence would spill to TEMP if they got too large