create private temporary table does not commit

View All Scripts Login to Run Script

Script Name create private temporary table does not commit
Description Normally, DDL such as "create table" will issue a commit in the session. CREATE PRIVATE TEMPORARY TABLE does not.
Area SQL General / DDL
Contributor Jeffrey Kemp
Created Monday February 19, 2018

Statement 1

select * from v$version


BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0 Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production 0

Statement 2
create an ordinary table
```
create table t (n number)
```
```
Table created.
```

BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production	Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0	Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production	0

Statement 3

do some ordinary dml

insert into t values (1)

1 row(s) inserted.

Statement 4

will this commit the insert into the ordinary table t?

create private temporary table ora$ptt_a (x number) on commit preserve definition

Table created.

Statement 5
rollback the insert
```
rollback
```
```
Statement processed.
```
Statement 6
```
select * from t
```
```
no data found
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database