Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle Create and Drop Global Temporary Table

Oracle support to create a global temporary table with the help of 'GLOBAL TEMPORARY' keyword. Oracle GLOBAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within Oracle sessions.

Oracle CREATE GLOBAL TEMPORARY TABLE Syntax

To create a global temporary table, use the following syntax:

Syntax

DECLARE GLOBAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ]
);

Parameters:

tablename: The temporary table name that you want to create.

column1, column2: The columns that you wish to create in the global temporary table.

Oracle CREATE GLOBAL TEMPORARY TABLE example

The following Oracle statement to create global temporary table:

Example

DECLARE GLOBAL TEMPORARY TABLE tblTempCustomer
( custfName varchar2(50) NOT NULL,
  custlName varchar2(50) NOT NULL,
  custAddress varchar2(50)
);

Note:

In the above example, we created global temporary table "tblTempCustomer".

Oracle DROP GLOBAL TEMPORARY TABLE Syntax

To remove a temporary table, use the following syntax:

Syntax

DROP TABLE temptablename PURGE;

Parameters:

temptablename: The temporary table name that you want to remove.

Oracle DROP GLOBAL TEMPORARY TABLE example

The following Oracle statement to remove existing global temporary table:

Example

DROP TABLE tblTempCustomer PURGE;

Note:

In the above example, we removed a global temporary table called "tblTempCustomer".