My Oracle Support Banner

Implicit commit - Create Private Temporary Table (Doc ID 3045954.1)

Last updated on SEPTEMBER 05, 2024

Applies to:

Autonomous Database Serverless - Version N/A and later
Information in this document applies to any platform.

Symptoms

Implicit Commit is performed when we create private temporary table with "ON COMMIT PRESERVE DEFINITION"

A private temporary table differs from a temporary table in that its definition and data are visible only within the session that created it. Use the ON COMMIT clause to define the scope of a private temporary table: either transaction or session. The ON COMMIT clause used with the keywords DROP DEFINITION creates a transaction-specific table whose data and definition are dropped when the transaction commits. This is the default behavior. The ON COMMIT clause used with keywords PRESERVE DEFINITION creates a session-specific table whose definition is preserved when the transaction commits. 

The DML operation performed on TEMP01 table is committed when we create private temporary ora$ptt_tab01 as shown below

SQL> create table temp01(i INT);
Table created.
SQL> insert into temp01 values(1);
1 row created.
SQL> create private temporary table ora$ptt_tab01 on commit preserve definition as select 1 as c from dual;
Table created.
SQL> rollback;
Rollback complete.
SQL> select * from temp01;
I
----------
1

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.