Temporary table creation in Oracle

Question

Is there any way to create a temporary table in Oracle which gets dropped by itself once the schema connection gets closed? Does local temporary table wok similar way?

There is no local temporary table in Oracle. And no, there are no temp tables that are automatically dropped. But why would you need that? — user330315
– user330315, Commented Sep 16, 2015 at 11:49
Thank you for the info! I have been working in Teradata where I had an option Volatile table which gets dropped automatically once the session ends. In case I wanna save the data for a small time and have to drop it once the work is done Volatile tables help a lot. — Vivek
– Vivek, Commented Sep 16, 2015 at 12:08
In most cases you don't really need a temporary table in Oracle. Using a CTE usually works just as well. — user330315
– user330315, Commented Sep 16, 2015 at 12:09

Radu Gheorghiu · Accepted Answer · 2015-09-16 13:46:23Z

1

I think the solution for your problem would be to use GTT's (Global Temporary Tables).

They will allow you to store temporary data in them, which will be available per session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)

Also, this data can be deleted by specifying a ON COMMIT DELETE ROWS; option at the end of the creation script, like:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

As mentioned on the website:

"The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction, or the end of the session."

You can also take a look at the Oracle docs here.

answered Sep 16, 2015 at 13:46

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pravin Satav · Accepted Answer · 2015-09-16 12:03:04Z

0

You can create temp table in oracle once and not every session. However, the rows you insert into it are visible only to your session, and are automatically deleted when you end you session or end of the transaction.

answered Sep 16, 2015 at 12:03

Pravin Satav

7025 gold badges18 silver badges37 bronze badges

Collectives™ on Stack Overflow

Temporary table creation in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related