Global Temporary Table
-- The data in global temporary table will get truncated at the
-- end of the session.

CREATE GLOBAL TEMPORARY TABLE gtt_current_transactions
ON COMMIT PRESERVE ROWS 
AS 
SELECT * FROM edw_sales_transactions
WHERE TO_CHAR(created_date,'yyyymmdd') = TO_CHAR(SYSDATE,'yyyymmdd'); 
Generic Temporary Table (CTAS)
-- This is a quick script to create a temporary/permanent table 
-- for data analysis/testing. Table has to be dropped by force or
-- data has to be truncated to clear the table.  User needs
-- create/drop table privileges and adequate tablespace. Using
-- PARALLEL option, parallel servers are used to create the table
-- and same degree of parallelism is used in querying the table.

CREATE TABLE tmp_current_transactions PARALLEL 
AS 
SELECT * FROM edw_sales_transactions
WHERE TO_CHAR(created_date,'yyyymmdd') = TO_CHAR(SYSDATE,'yyyymmdd'); 

-- To create a blank table use a false condition (2 < 1)

CREATE TABLE tmp_current_transactions PARALLEL 
AS 
SELECT * FROM edw_sales_transactions
WHERE 2 < 1;
Other Types of Tables
External Table
Index Organized Table (IOT)
Nested Table
Temporary Table
XML Table


Oracle registered trademark of Oracle Corporation.

Last Revised On: September 01, 2014

  1475