Global Temp Tables (GTT)

  • Data Stored in GTT are temporary
  • The data stored in GTT exist only as long as the session or Transaction. At the end all the data is automatically deleted.
  • Data is private for each sessions.
  • Table definition is viewed by all sessions.
  • Temporary data is stored into default temp tablespace, which can also be specified while creating the table definition.
  • We can perform insert update and delete on these tables.

Uses of GTT

  • When we need to perform high level calculation and for temporary purpose and store the value we used GTT
  • The data need not be persisted
  • Other users need not see the data in the table

Example :

create GLOBAL TEMPORARY TABLE GTT_COUNTRY(
COUNTRY_ID NUMBER,
COUNTRY_NAME VARCHAR2(30)
) ON COMMIT PRESERVE ROWS; --Data is present until the connection/session exist
--) ON COMMIT DELETE ROWS; --Data is deleted on commit

select * from GTT_COUNTRY;

insert into GTT_COUNTRY values (1,'India');
insert into GTT_COUNTRY values (2,'USA');
insert into GTT_COUNTRY values (3,'UK');

--insert update delete can be performed on GTT

Reference :
Ref1
Ref2

Leave a Comment