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.
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.
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.