PRIVATE TEMPORARY TABLE
Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the ON COMMIT clause.
With the introduction of private temporary tables, Oracle has an option similar to that seen in other engines (SQL Server), where the table object itself is temporary, not just the data.
Private temporary tables are memory-based, so there is no metadata recorded in the data dictionary. As a result you can’t use the USER_TABLES view to display the list of private temporary tables in the current session. The following views are available to display information about private temporary tables.
DBA_PRIVATE_TEMP_TABLES ::: All private temporary tables in the database.
USER_PRIVATE_TEMP_TABLES ::: Private temporary tables in the current session.
• The table name must begin with the prefix defined in the PRIVATE_TEMP_TABLE_PREFIX initialisation parameter. The default is “ORA$PTT_”.
• Indexes, materialized views, and zone maps are not allowed on private temporary tables.
• Primary keys, or any constraint that requires an index, are not allows on private temporary tables.
• Columns can’t have default values.
• Private temporary tables can’t be accessed via database links.
The ON COMMIT DROP DEFINITION clause, the default, indicates the table should be dropped at the end of the transaction, or the end of the session.
SQL> CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
2 id NUMBER,
3 description VARCHAR2(20)
4 )
5 ON COMMIT DROP DEFINITION;
Table created.
SQL> SELECT table_name,tablespace_name FROM dba_private_temp_tables WHERE LOWER(table_name) = 'ora$ptt_my_temp_table';
TABLE_NAME TABLESPACE_NAME
--------------------------- -------------------------
ORA$PTT_MY_TEMP_TABLE TEMP
SQL> INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');
1 row created.
SQL> SELECT COUNT(*) FROM ora$ptt_my_temp_table;
COUNT(*)
----------
1
SQL> COMMIT;
Commit complete.
SQL> SELECT table_name,tablespace_name FROM dba_private_temp_tables WHERE LOWER(table_name) = 'ora$ptt_my_temp_table';
no rows selected
SQL> SELECT COUNT(*) FROM ora$ptt_my_temp_table;
SELECT COUNT(*) FROM ora$ptt_my_temp_table
*
ERROR at line 1:
ORA-00942: table or view does not existThe 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_my_temp_table (
2 id NUMBER,
3 description VARCHAR2(20)
4 )
5 ON COMMIT PRESERVE DEFINITION;
Table created.
SQL> SELECT table_name,tablespace_name FROM user_private_temp_tables WHERE LOWER(table_name) = 'ora$ptt_my_temp_table';
TABLE_NAME TABLESPACE_NAME
--------------------------- -------------------------
ORA$PTT_MY_TEMP_TABLE TEMP
SQL> INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');
1 row created.
SQL> SELECT COUNT(*) FROM ora$ptt_my_temp_table;
COUNT(*)
----------
1
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM ora$ptt_my_temp_table;
COUNT(*)
----------
1
SQL> SELECT table_name,tablespace_name FROM user_private_temp_tables WHERE LOWER(table_name) = 'ora$ptt_my_temp_table';
TABLE_NAME TABLESPACE_NAME
--------------------------- -------------------------
ORA$PTT_MY_TEMP_TABLE TEMP
SQL> DISCONNECT
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CONNECT
Enter user-name: course
Enter password:
Connected.
SQL> SELECT table_name,tablespace_name FROM user_private_temp_tables WHERE LOWER(table_name) = 'ora$ptt_my_temp_table';
no rows selected
SQL> SELECT COUNT(*) FROM ora$ptt_my_temp_table;
SELECT COUNT(*) FROM ora$ptt_my_temp_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
--The above output shows the table persists beyond the commit, but is dropped when we disconnect and create a new session.Private temporary tables can also be created using the CTAS method.
SQL> CREATE PRIVATE TEMPORARY TABLE ora$ptt_emp AS
2 SELECT * FROM emp;
Table created.
SQL> SELECT * FROM ora$ptt_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5001 10
7698 BLAKE MANAGER 7839 01-MAY-81 2851 30
7782 CLARK MANAGER 7839 09-JUN-81 2451 10
7566 JONES MANAGER 7839 02-APR-81 2976 20
7788 SCOTT ANALYST 7566 19-APR-87 3001 20
7902 FORD ANALYST 7566 03-DEC-81 3001 20
7369 SMITH CLERK 7902 17-DEC-80 801 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1601 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1251 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1251 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1501 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1101 20
7900 JAMES CLERK 7698 03-DEC-81 951 30
7934 MILLER CLERK 7782 23-JAN-82 1301 10
14 rows selected.It doesn’t make sense for a permanent PL/SQL object to directly reference a temporary object, as it would not exist at compile time. If you want to use a private temporary table from a permanent object it would have to be done using dynamic SQL.
SQL> CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER)
2 RETURN VARCHAR2
3 AS
4 l_sql VARCHAR2(32767);
5 l_return VARCHAR2(30);
6 BEGIN
7 l_sql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
8 id NUMBER,
9 description VARCHAR2(20)
10 )
11 ON COMMIT DROP DEFINITION';
12
13 EXECUTE IMMEDIATE l_sql;
14
15 EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE')]';
16
17 EXECUTE IMMEDIATE 'SELECT description INTO :l_return FROM ora$ptt_my_temp_table WHERE id = :id' INTO l_return USING p_id;
18
19 RETURN l_return;
20 END;
21 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.put_line('ptt_test(1) = ' || ptt_test(1));
3 END;
4 /
--OUTPUT :::
ptt_test(1) = ONE
