What are Global Temporary Tables in Oracle?
asked
Global Temporary Tables
It's discussed at Oracle-Base, among other places...
The table definition lives in the data dictionary and the structure can be seen by everyone but any data you put in it is private to you, no one else sees it. The space required for data is allocated in temp tablespace.
And, after it has been created once, it's available to all users (no need to do a create in your process, it's already there).
Let's say you had a process that required a temp table and that many people executed this process. You could create a global temp table (t1)
create global temporary table t1 (<column list>) on commit preserve rows;
and refer to it and use it freely at the same time that others did but you'd only see the data you populated into it.
insert into t1 select * from (complicated query logic)
-- 283 rows inserted
select * from t1
-- 283 rows display
At the same time, other people might be using the table to store 1 or 1,000,000 rows but... you'd never know it. You'd think the table was private to you, you see only your 283 rows.
Teradata has something similar.
And then I used one to answer the question on windowing and analytic functions but I forgot the "on commit preserve rows" clause in an autocommit environment and immediately after my insert statements, the table automatically reset itself to empty. The situation was a bit confusing, until I realized what I had done.
