Oracle Private Temporary Table

 

In Oracle Database, global temporary tables  are permanent objects whose data are stored on disk and automatically deleted at the end of a session or transaction. In addition, global temporary tables are visible to all sessions currently connected to the database.

Oracle 18c introduced private temporary tables whose both table definition and data are temporary and are dropped at the end of a transaction or session. On top of that, Oracle stores private temporary tables in memory and each temporary table is only visible to the session which created it.

Naming rules of the private temporary tables

All private temporary tables have a prefix defined by the PRIVATE_TEMP_TABLE_PREFIX initialization parameter, which defaults to to ORA$PTT_.

Create private temporary table statement

To create a new private temporary table, you use the CREATE PRIVATE TEMPORARY TABLE statement:

 

CREATE PRIVATE TEMPORARY TABLE table_name(

    column_definition,

    ...

) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION]; 

In this syntax:

First, specify the name of the temporary table, which follows the naming rule mentioned above.

Second, specify a list of columns with their definitions.

Third, use the ON COMMIT clause to indicate whether the table is transaction-specific or session-specific:

·       The ON COMMIT DROP DEFINITION option creates a private temporary table that is transaction-specific. At the end of the transaction, Oracle drops both table definition and data.

·       The ON COMMIT PRESERVE DEFINITION option creates a private temporary table that is session-specific. Oracle removes all data and drops the table at the end of the session.

By default, Oracle uses ON COMMIT DROP DEFINITION if you omit the ON COMMIT option.

Private temporary tables vs. global temporary tables

This table illustrates the differences between global temporary tables and private temporary tables:

Characteristic	Global temporary tables	Private temporary tables
Naming rule	Same as for permanent tables	By default, must be prefixed with ORA$PTT_ .
Visibility	All sessions	Only the session that created the table.
Storages	Disk	Memory only
Table types	Transaction-specific (ON COMMIT DELETE ROWS) or session-specific (ON COMMIT PRESERVE ROWS)	Transaction-specific (ON COMMIT DROP DEFINITION) or session-specific (ON COMMIT PRESERVE DEFINITION)

Oracle private temporary table examples

Let’s take some examples of using the CREATE PRIVATE TEMPORARY TABLE statement.

1) Creating a private temporary table example which is transaction-specific

First, create a new temporary table that is transaction-specific:

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp1(

    id INT,

    description VARCHAR2(100)

) ON COMMIT DROP DEFINITION;

 

Next, insert a row into the ora$ppt_temp1 table:

INSERT INTO ora$ppt_temp1(id,description)

VALUES(1,'Transaction-specific private temp table'); 

Then, view the contents of the ora$ppt_temp1 table: 

SELECT id, description FROM ora$ppt_temp1;

 After that, commit the transaction:

COMMIT;

 Finally, query data from the ora$ppt_temp1 table:

 

SELECT id, description FROM ora$ppt_temp1;

 

Oracle issued the following error because it already dropped the ora$ppt_temp1 table at the end of the transaction.

ORA-00942: table or view does not exist

2) Creating a private temporary table which is session-specific

First, create a new temporary table that is session specific:

CREATE PRIVATE TEMPORARY TABLE ora$ppt_temp2(

    id INT,

    description VARCHAR2(100)

) ON COMMIT PRESERVE DEFINITION; 

Next, insert a row into the ora$ppt_temp2 table: 

INSERT INTO ora$ppt_temp2(id,description)

VALUES(1,'Session-specific private temp table'); 

Then, view data of the ora$ppt_temp2 table: 

SELECT id, description

FROM ora$ppt_temp2;

After that, commit the transaction and check contents of the ora$ppt_temp2 table:

COMMIT;

 SELECT id, description FROM ora$ppt_temp2; 

Finally, reconnect and view the contents of the ora$ppt_temp2 table:

 SELECT id, description FROM ora$ppt_temp2;

 

Oracle issued the following error because it removed the ora$ppt_temp2 table at the end of the session.

ORA-00942: table or view does not exist

Private temporary table Limitations

Private temporary tables share the same limitations of global temporary tables and the following additional restrictions:

·       The table name must have a prefix defined in the PRIVATE_TEMP_TABLE_PREFIX initialization parameter which is default to ORA$PTT_.

·       Permanent database objects cannot directly reference private temporary tables.

·       Indexes and materialized views cannot be created on private temporary tables.

·       Columns of the private temporary table cannot have default values.

·       Private temporary tables cannot be accessed via database links.

 

Oracle global temporary tables

 A temporary table is a table that holds data only for the duration of a session or transaction.

Global temporary tables in Oracle are permanent database objects that store data on disk and visible to all sessions.

However, the data stored in the global temporary table is private to the session. In other words, each session can only access its own data in the global temporary table.

Note that Oracle 18c introduced the private temporary table, which is a memory-based temporary table that is automatically dropped at the end of a session or transaction.

CREATE GLOBAL TEMPORARY TABLE statement

To create a global temporary table, you use the CREATE GLOBAL TEMPORARY TABLE statement as follows: 

 

CREATE GLOBAL TEMPORARY TABLE table_name (  

column_definition,

    ...,

    table_constraints

) ON COMMIT [DELETE ROWS | PRESERVE ROWS]; 

The ON COMMIT clause specifies whether data in the table is transaction-specific or session-specific:

·       The ON COMMIT DELETE ROWS clause specifies that the global temporary table is transaction-specific. It means that Oracle truncates the table (remove all rows) after each commit.

·       The ON COMMIT PRESERVE ROWS clause specifies that the global temporary table is session-specific, meaning that Oracle truncates the table when you terminate the session, not when you commit a transaction.

Oracle uses the ON COMMIT DELETE ROWS option by default if you omit the ON COMMIT clause.

Creating a global temporary table examples

Let’s practice with some examples of creating a global temporary table.

1) Creating a transaction-specific global temporary table example

First, create a transaction-specific global temporary table using the ON COMMIT DELETE ROWS option:

CREATE GLOBAL TEMPORARY TABLE temp1(

    id INT,

    description VARCHAR2(100)

) ON COMMIT DELETE ROWS;

 Next, insert a new row into the temp1 table:

 INSERT INTO temp1(id,description)

VALUES(1,'Transaction specific global temp table');

Then, query data from the temp1 table: 

SELECT id, description FROM temp1; 

After that, commit the transaction:

COMMIT;

Finally, view the contents of the temp1 table again: 

SELECT id, description

FROM temp1; 

It returned no row because Oracle truncated all rows of the temp1 table after the transaction commit.

2) Creating a session-specific global temporary table example

First, create a session-specific global temporary table:

CREATE GLOBAL TEMPORARY TABLE temp2(

    id INT,

    description VARCHAR2(100)

) ON COMMIT PRESERVE ROWS; 

Second, insert a new row into the temp2 table:

 

 INSERT INTO temp2(id,description) VALUES(1,'Session specific global temp table'); 

Third, commit the transaction:

COMMIT; 

Fourth, view the contents of the temp2: 

SELECT id, description FROM temp2;

Fifth, commit the transaction:

COMMIT; 

Finally, disconnect the current session, connect to the database using a separate session, and check the content of the temp2table: 

SELECT id, description FROM temp2; 

It returned no row because Oracle truncated all rows of the temp2 table after the session ended.

Oracle global temporary tables & indexes

Oracle allows you to create indexes on global temporary tables.

However, the data in the index has the same scope as the data stored in the global temporary table, which exists during a transaction or session.

Global temporary table tables & tablespaces

By default, Oracle stores the data of the global temporary table in the default temporary tablespace of the table’s owner.

But you can explicitly assign another tablespace to a global temporary table during table creation using the TABLESPACE clause as shown in the following statement:

 

 CREATE GLOBAL TEMPORARY TABLE table_name (

    column_definition,

    ...,

    table_constraints

) ON COMMIT [DELETE ROWS | PRESERVE ROWS]

TABLESPACE tablespace_name;

This allows you to better manage temporary tablespaces.

Things to consider before creating a global temporary table:

These are the most important points to consider before you create a global temporary table.

1) DDL operation on global temporary tables

It is not possible to perform a DDL operation (except TRUNCATE) on an existing global temporary table if one or more sessions are currently bound to that table.

 First, create a temporary table named temp3:

CREATE GLOBAL TEMPORARY TABLE temp3(

    id INT

) ON COMMIT DELETE ROWS; 

Next, insert a new row into the temp3 table:

INSERT INTO temp3(id) VALUES(1);

 Then, log in to the database in a separate session e.g., using SQL*Plus and add a column to the temp3 table:

 ALTER TABLE temp3

ADD description VARCHAR2(100);

 Oracle issued the following error:

ORA-14450: attempt to access a transactional temp table already in use

 After that, commit the transaction in the first session:

COMMIT;

Finally, perform the DDL operation in the second transaction:

ALTER TABLE temp3

ADD description VARCHAR2(100);

 This time it worked because no session is currently bound to the temp3 table.

2) Transactions on transaction-specific global temporary tables

Oracle only allows one transaction at a time on a transaction-specific temporary table.

If you have several autonomous transactions in a single transaction scope, you must commit the previous autonomous transaction before the next transaction can use the table.

3) Rollback on transaction-specific global temporary tables

Rolling back (ROLLBACK) on the global temporary table will cause all data entered lost.

4) Backup & recovery on global temporary tables

Due to the nature of temporary tables, backup and recovery are not available in case of a system failure.