Live SQL

Global temporary tables - ORA-14450

View All Public Scripts Login and Run Script

Script Name Global temporary tables - ORA-14450
Visibility Unlisted - anyone with the share link can access
Description In previous versions (up to and including 11g R2), the autonomous transaction used to raise error ORA-14450.
Area PL/SQL General
Contributor
Created Sunday December 29, 2019

Statement 1

create global temporary table gtt ( x int ) on commit delete rows

Table created.

Statement 2
Uncommitted main transaction
```
insert into gtt values (1) 
```
1 row(s) inserted.
Statement 3
```
select * from gtt 
```
X
1
Statement 4
In older versions (up to 11g R2), this autonomous transaction used to raise error ORA-14450: attempt to access a transactional temp table already in use In 19c it does not raise error.
```
declare 
   pragma autonomous_transaction; 
begin 
   insert into gtt values ( 2 ); 
   commit; 
end; 
```
Statement processed.
Statement 5
Committing the autonomous transaction did also remove the uncommitted row from the main transaction.
```
select * from gtt 
```
no data found

Additional Information

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