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?

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