Private Temporary Tables

Private Temporary Tables

O Oracle Database 18c introduziu o conceito de Private Temporary Tables (tabelas temporárias privadas) que são objetos temporários de banco de dados e que são descartados ao final de uma transação ou sessão.

As tabelas temporárias privadas são armazenadas na memória e cada uma é visível apenas para a sessão que a criou.

Quando uma tabela temporária privada é criada com a clausula ON COMMIT DROP DEFINITION, indica que todos os dados na tabela serão perdidos e a tabela será eliminada após a confirmação e termino da transação.

GPO@morpheus> create private temporary table TESTE (
  col_a number,
  col_b varchar2(25)) on commit drop definition;

create private temporary table TESTE (
*
ERROR at line 1:
ORA-00903: invalid table name

O parâmetro PRIVATE_TEMP_TABLE_PREFIX, cujo padrão é “ORA$PTT_“, define o prefixo que deve ser usado ao criar uma tabela temporária privada.

No exemplo acima, foi criado uma tabela temporária privada sem usar o prefixo correto no nome, o que resultou no erro (ORA-00903: invalid table name).

 GPO@morpheus> show parameter private_temp_table_prefix
  
 NAME                                TYPE       VALUE
 ------------------------------------ ----------- ------------
 private_temp_table_prefix           string     ORA$PTT_
  
 GPO@morpheus> create private temporary table ORA$PTT_TESTE (
   2 col_a number,
   3 col_b varchar2(25)) on commit drop definition;
  
 Table created.
  
 GPO@morpheus> insert into ORA$PTT_TESTE values (1,'Private Temporary Table');
  
 1 row created.
  
 GPO@morpheus> select * from ORA$PTT_TESTE;
  
      COL_A COL_B
  ---------- -------------------------
           1 Private Temporary Table
 
 GPO@morpheus> commit;
  
 Commit complete.
  
 GPO@morpheus> select * from ORA$PTT_TESTE;
  
 select * from ORA$PTT_TESTE
               *
  
 ERROR at line 1:
  
 ORA-00942: table or view does not exist 

O próximo exemplo irei modificar o parâmetro PRIVATE_TEMP_TABLE_PREFIX de “ORA$PTT_” para “PRIVATE_”

 GPO@morpheus> select value, isses_modifiable, issys_modifiable, ispdb_modifiable 
 from v$parameter where name = 'private_temp_table_prefix';
  
 VALUE          ISSES ISSYS_MOD ISPDB
 --------------- ----- --------- -----
 ORA$PTT_       FALSE DEFERRED TRUE       
  
 GPO@morpheus> alter system set private_temp_table_prefix = 'PRIVATE_' deferred;
  
 alter system set private_temp_table_prefix = 'PRIVATE_' deferred
  
 *
  
 ERROR at line 1:
  
 ORA-32017: failure in updating SPFILE
  
 ORA-32465: value specified for the parameter is incorrect

O erro ocorre porque o valor de prefixo deve começar sempre com “ORA$” e deve ser exclusivo no banco de dados.

 GPO@morpheus> alter system set private_temp_table_prefix = 'ORA$PRIVATE_' deferred;
  
 System altered.  

Após alteração do parâmetro, agora irei efetuar a criação de uma tabela temporária privada com a clausula ON COMMIT PRESERVE DEFINITION, isso significa que todos os dados na tabela serão preservados ao final da transação porem a tabela será eliminada no termino da sessão.

GPO@morpheus> create private temporary table ORA$PRIVATE_TESTE (
   2  col_a number,
   3  col_b varchar2(25)) on commit preserve definition;
 
Table created.
 
GPO@morpheus> insert into ORA$PRIVATE_TESTE values (1,'Private Temporary Table');
 
1 row created.
 
GPO@morpheus> commit;
 
Commit complete.
 
GPO@morpheus> select * from ORA$PRIVATE_TESTE;

     COL_A COL_B
---------- -------------------------
        1 Private Temporary Table
 
GPO@morpheus> disc
 
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
GPO@morpheus> conn gpo@morpheus
Enter password: *******
 
Connected.
 
GPO@morpheus> select * from ORA$PRIVATE_TESTE;
 select * from ORA$PRIVATE_TESTE
               *
 
 ERROR at line 1:
 
 ORA-00942: table or view does not exist 

O exemplo acima mostra que a tabela persistiu após o COMMIT, mas foi descartada após ser encerrada a sessão.

As tabelas temporárias privadas são baseadas em memória, portanto, não há metadados registrados no dicionário de dados.

Views:

• CDB_PRIVATE_TEMP_TABLES
• DBA_PRIVATE_TEMP_TABLES
• USER_PRIVATE_TEMP_TABLES

Observação importante sobre PRIVATE TEMPORARY TABLES (PTT):

Referências

• Oracle Database 18c – Database Reference.
• Oracle Database 18c – New Features Guide.
• Oracle Database 18c – Database Administrator’s Guide.
• Oracle Live SQL – Private Temporary Tables.
• My Oracle Support (MOS) – 18.1 New Feature Private Temporary Tables (Doc ID 2372875.1).