How to Create Global Temp Table in Oracle

Here are SQL queries to create Global temp table in Oracle.

How to create only Global temp table structure

Here I have created the customer table and inserted rows.

--create table customer (name char(20), cust_id number);
--insert into customer values('SRINI', 100001);
--insert into customer values('AARTI', 1022201);
--select * from customer;

The output from the customer table.

NAME	CUST_ID
SRINI	100001
AARTI	1022201

SQL query to create a Temporary table

In the SQL query, the global keyword is mandatory. When you run the query, a table my_temp will create that has the customer’s table structure.

create global temporary TABLE my_temp 
as select * from customer;
--
desc my_temp;
select count(*) from my_temp;

It displays the table structure and count of rows in the output. The count zero means no rows are present in the temp table. It is how you can create only a temp table structure.

If we are ever to gain freedom, it must be by conquering nature, never by running away. Cowards never win victories. We have to fight fear and troubles and ignorance if we expect them to flee before us.

Swami vivekanada

TABLE MY_TEMPResult Set 5
Column	Null?	Type
NAME	 - 	CHAR(20)
CUST_ID	 - 	NUMBER
2 rows selected.
Result Set 6
COUNT(*)
0

How to create Global temp table structure and data

I have added a statement ‘on commit preserve rows’ that preserves rows. For verification, issue the select statement that displays the table data.

create global temporary TABLE my_temp1 
on commit preserve rows
as select * from customer;
--
select * from my_temp1;

Here’s the output from the my_temp1 table.

NAME	CUST_ID
SRINI	100001
AARTI	1022201

Spotlight: 
Here are two ways to create a temporary table in Oracle. 

Related