Understanding Private Temporary Table In Oracle

ptt

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 exist

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_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

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “PL/SQL Collection Methods”

  1. When you pass an associative array as a parameter to a remote database using a database link, the two databases can have different globalization settings. When the remote database uses a collection method such as An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically (however, a maximum limit is imposed see Referencing Collection Elements ).

Leave a Reply

Your email address will not be published. Required fields are marked *