CODEWITHSUNDEEP
sqloracle-databasetemp-tables
GigaPr
GigaPr

Reputation: 5386

How do you create a temporary table in an Oracle database?

I would like to create a temporary table in a Oracle database

something like 

Declare table @table (int id)

In SQL server

And then populate it with a select statement

Is it possible?

Thanks

Upvotes: 129

Views: 704509

Answers (5)

Hakuna Patata
Hakuna Patata

Reputation: 59

If you are trying to create something similar to SQL Server where you SELECT INTO #TEMP_TABLE_NAME this cannot be achieved in Oracle using CREATE TABLE unless you have security to do so which is probably unlikely if you are a regular report writer.

In Oracle, if you are trying to store the output of a SELECT block in memory to increase query performance, you'll need to use the Oracle hint /+ MATERIALIZE/ in the main SELECT of a CTE. This will store the results of the CTE in memory and can be accessed in other parts of your query without running the risk of re-executing the CTE each time it is referenced.

Example:

WITH CTE AS (
    SELECT /*+ MATERIALIZE*/
    'FOO' AS "STUFF"
    FROM DUAL
)

SELECT * FROM CTE

If you check the execution plan after, you'll see that the object will be read in as "Cursor Duration Memory".

Upvotes: 4

Edy Muniz
Edy Muniz

Reputation: 1

CREATE TABLE table_temp_list_objects AS
SELECT o.owner, o.object_name FROM sys.all_objects o WHERE o.object_type ='TABLE';

Upvotes: -5

Matthew Watson
Matthew Watson

Reputation: 14253

Just a tip.. Temporary tables in Oracle are different to SQL Server. You create it ONCE and only ONCE, not every session. The rows you insert into it are visible only to your session, and are automatically deleted (i.e., TRUNCATE, not DROP) when you end you session ( or end of the transaction, depending on which "ON COMMIT" clause you use).

Upvotes: 85

hamishmcn
hamishmcn

Reputation: 7981

Yep, Oracle has temporary tables. Here is a link to an AskTom article describing them and here is the official oracle CREATE TABLE documentation.

However, in Oracle, only the data in a temporary table is temporary. The table is a regular object visible to other sessions. It is a bad practice to frequently create and drop temporary tables in Oracle.

CREATE GLOBAL TEMPORARY TABLE today_sales(order_id NUMBER)
ON COMMIT PRESERVE ROWS;

Oracle 18c added private temporary tables, which are single-session in-memory objects. See the documentation for more details. Private temporary tables can be dynamically created and dropped.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_today_sales AS
SELECT * FROM orders WHERE order_date = SYSDATE;

Temporary tables can be useful but they are commonly abused in Oracle. They can often be avoided by combining multiple steps into a single SQL statement using inline views.

Upvotes: 175

harish from nit ameerpet
harish from nit ameerpet

Reputation: 421

CREATE GLOBAL TEMPORARY TABLE Table_name
    (startdate DATE,
     enddate DATE,
     class CHAR(20))
  ON COMMIT DELETE ROWS;

Upvotes: 42

Related Questions