Oracle 18c-Private Temporary Tables

Oracle 18c-Private Temporary Tables

Description:-

• In this article we are going to see Oracle 18c-Private Temporary Tables.
• It is Available from Oracle 18c
• A temporary table holds data that exists only for the duration of a transaction or session.
• You cannot create a PTT as SYS and possibly with other privileged accounts. If you try to do so the error message you get will be misleading: Ignore it and move to a non-privileged schema.
• Private temporary tables are useful for dynamic reporting applications.
• Memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.

Some Restrictions on Private Temporary Tables Here

• The name of private temporary tables must always be prefixed with whatever is defined with the init.ora parameter PRIVATE_TEMP_TABLE_PREFIX. The default is ORA$PTT_.
• You cannot create indexes, materialized views, or zone maps on private temporary tables.
• You cannot define column with default values.
• You cannot reference private temporary tables in any permanent object, e.g. views or triggers.
• Private temporary tables are not visible through database links.

Private Temporary Table Characteristics:-

prerequisite:-

[oracle@testdb ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 18.0.0.0.0 Production on Mon Jul 2 06:11:39 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX

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

Let’s start the Demo:-

Creating a Private Temporary Table

• ON COMMIT DROP DEFINITION

Note:-This is default ON COMMIT DROP DEFINITION clause,its indicates that the table should be dropped at the end of the transaction, or the end of the session.

[oracle@testdb ~]$ sqlplus hari/hari@pdb1
SQL*Plus: Release 18.0.0.0.0 Production on Mon Jul 2 06:27:41 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Mon Jul 02 2018 06:27:20 +00:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> CREATE PRIVATE TEMPORARY TABLE ora$ptt_Hari_temp_table (
sal NUMBER,name VARCHAR2(10))
ON COMMIT DROP DEFINITION; 

Table created.

SQL> INSERT INTO ora$ptt_hari_temp_table VALUES (100, 'hari');
1 row created.

SQL> select * from ora$ptt_hari_temp_table;
SAL NAME
---------- ----------
100 hari

When you giving a commit the PTT table is dropped

SQL> commit;
Commit complete.

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

• ON COMMIT PRESERVE DEFINITION

Note:-The ON COMMIT PRESERVE DEFINITION clause indicates the table and any data should persist beyond the end of the transaction. The table will be dropped at the end of the session.

SQL> CREATE PRIVATE TEMPORARY TABLE ora$ptt_Hari_temp_table (
sal NUMBER,name VARCHAR2(10))
ON COMMIT PRESERVE DEFINITION; 2 3

Table created.

SQL> INSERT INTO ora$ptt_hari_temp_table VALUES (100, 'hari');
1 row created.

SQL> select * from ora$ptt_hari_temp_table;
SAL NAME
---------- ----------
100 hari

SQL> commit;
Commit complete.

SQL> select * from ora$ptt_hari_temp_table;
SAL NAME
---------- ----------
100 hari

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
[oracle@testdb ~]$ sqlplus hari/hari@pdb1

SQL*Plus: Release 18.0.0.0.0 Production on Mon Jul 2 06:42:38 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Mon Jul 02 2018 06:27:41 +00:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

The below output shows the table persists beyond the commit, but is dropped when we disconnect and create a new session.

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

Display the information about private temporary tables.

• DBA_PRIVATE_TEMP_TABLES
• USER_PRIVATE_TEMP_TABLES

SQL> CREATE PRIVATE TEMPORARY TABLE ora$ptt_Hari_temp_table (
sal NUMBER,name VARCHAR2(10))
ON COMMIT PRESERVE DEFINITION; 2 3
Table created.

SQL> col owner for a20
SQL> col table_name for a30
SQL> select owner,table_name from USER_PRIVATE_TEMP_TABLES;

OWNER                   TABLE_NAME
-------------------- ------------------------------
HARI                     ORA$PTT_HARI_TEMP_TABLE

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-tables.html#GUID-707B02F5-E589-4C20-8E2E-5ED4F7888702

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba