private-temporary-tables-18c

Private Temporary Tables in Oracle Database 18c

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 setup.

If you've come to Oracle from a different database engine, like SQL
Server, you might be confused by Oracle's idea of temporary tables. In
SQL Server developers will regularly create a temporary table to do
some work and drop it. In Oracle a Global Temporary Table (GTT) is a
permanent metadata object that holds rows in temporary segments on a
transaction-specfic or session-specific basis. It is not considered
normal to create and drop GTTs on the fly. With the introduction of
private temporary tables, Oracle has an option similar to that seen in
other engines, where the table object itself is temporary, not just the
data.
* [46]Temporary Tables
* [47]Naming Private Temporary Tables
* [48]Creation of Private Temporary Tables
* [49]Private Temporary Tables and PL/SQL
* [50]Views
* [51]Restrictions

Related articles.
* [52]Global Temporary Tables

Temporary Tables

Oracle support two types of temporary tables.
* Global Temporary Tables : Available since Oracle 8i. Discussed
[53]here.
* Private Temporary Tables : Available since Oracle 18c and subject
of this article.

Naming Private Temporary Tables

The PRIVATE_TEMP_TABLE_PREFIX initialisation parameter, which defaults
to "ORA$PTT_", defines the prefix that must be used in the name when
creating the private temporary table. In the following example we
create a private temporary table without using the correct prefix in
the name, which results in an error.
CREATE PRIVATE TEMPORARY TABLE my_temp_table (
id NUMBER,
description VARCHAR2(20)
);

CREATE PRIVATE TEMPORARY TABLE my_temp_table (
*
ERROR at line 1:
ORA-00903: invalid table name

SQL>

Creation of Private Temporary Tables

The syntax for creating a private temporary table will look familiar if
you have used global temporary tables.

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.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
id NUMBER,
description VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;


-- Insert, but don't commit, then check contents of PTT.
INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)
----------
1

SQL>

-- Commit and check contents.
COMMIT;

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

SQL>

In contrast, 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.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
id NUMBER,
description VARCHAR2(20)
)
ON COMMIT PRESERVE DEFINITION;


-- Insert, but don't commit, then check contents of PTT.
INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)
----------
1

SQL>


-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)
----------
1

SQL>


-- Reconnect and check contents of GTT.
CONN test/test@pdb1

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

SQL>

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

Private Temporary Tables and PL/SQL

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. The following silly example
creates a stored function which uses a private temporary table.
CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER)
RETURN VARCHAR2
AS
l_sql VARCHAR2(32767);
l_return VARCHAR2(30);
BEGIN
l_sql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
id NUMBER,
description VARCHAR2(20)
)
ON COMMIT DROP DEFINITION';

EXECUTE IMMEDIATE l_sql;

EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE')]';

EXECUTE IMMEDIATE 'SELECT description INTO :l_return FROM ora$ptt_my_temp_tabl
e WHERE id = :id' INTO l_return USING p_id;

RETURN l_return;
END;
/

The function works as expected.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('ptt_test(1) = ' || ptt_test(1));
END;
/
ptt_test(1) = ONE

PL/SQL procedure successfully completed.

SQL>

Views

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.
* [54]DBA_PRIVATE_TEMP_TABLES : All private temporary tables in the
database.
* [55]USER_PRIVATE_TEMP_TABLES : Private temporary tables in the
current session.

Restrictions

Private temporary tables share the limitations of global temporary
tables ([56]see here), but there are also additional restrictions.
* The table name must begin with the prefix defined in the
PRIVATE_TEMP_TABLE_PREFIX initialisation parameter. The default is
"ORA$PTT_".
* Permanent objects can't reference private temporary tables
directly.
* Indexes, materialized views, and zone maps are not allowed on
private temporary tables.
* Columns can't have default values.
* Private temporary tables can't be accessed via database links.

For more information see:
* [57]Creating a Temporary Table
* [58]CREATE TABLE
* [59]Global Temporary Tables

Hope this helps. Regards Tim...
--------------------------
Source .... : https://oracle-base.com/articles/18c/private-temporary-tables-18c
--------------------------