What is the difference between a temporary table vs global temporary table in Oracle?

Question

I have heard these two terms "temporary table" and "global temporary table" used pretty much in similar context.

What is the difference between the two?

Answer 1

In Oracle there isn't any difference. When you create a temporary table in an Oracle database, it is automatically global, and you are required to include the "Global" key word.

The SQL standard, which defines how the term "GLOBAL TEMPORARY TABLE" is interpreted, allows for either a LOCAL or GLOBAL scope. This would allow for either a user specific table (LOCAL) or everyone (GLOBAL). Oracle implements only the GLOBAL version.

The data you put into an Oracle Temporary table is specific to your session. That is, only you can see your data even if there are 100 users all using the same table, and your data is deleted from the table when you disconnect (or when you commit the current transaction) depending upon table settings.

Contrast this with MS SQL-Server, where temporary tables are local. If you create one, no one besides you knows that your temporary table exists. In Oracle, creating the temporary table allows everyone (well everyone with access to your schema) to see the table. When you log out of your session, the SQL-Server table is deleted and will need to be recreated for the next session. In Oracle, the temporary table is now a permanent part of your schema, even if the data isn't.

Answer 2

Contrast this with MS SQL-Server, where temporary tables are local. If you create one, no one besides you knows that your temporary table exists. In Oracle, creating the temporary table allows everyone (well everyone with access to your schema) to see the table. When you log out of your session, the SQL-Server table is deleted and will need to be recreated for the next session. In Oracle, the temporary table is now a permanent part of your schema, even if the data isn't (if not so you can decide whether to preserve it). The Oracle supports only global temporary table saves you from having to create the table in each session; it 'exists' but it is empty, and its content is unique (and private) per session.

Answer 3

Be aware that a global temporary table has no statistics associated with it, so look into whether the dynamic sampling level for the instance should be set to ensure that unanalyzed tables are sampled at parse time. Otherwise the heuristics can lead to a nasty execution plan.

Answer 4

Just to add to existing answers about local and global temporary tables, from Oracle 18c there will be trully "Private Temporary Tables":

Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session. A private temporary table is stored in memory and is visible only to the session that created it.

A private temporary table confines the scope of a temporary table to a session or a transaction, thus providing more flexibility in application coding, leading to easier code maintenance and a better ready-to-use functionality.

Demo from Oracle Live SQL: 18c private temporary tables:

-- Private temporary tables must be prefixed as per the database parameter 
-- 'private_temp_table_prefix' 

create private temporary table ORA$PTT_MY_TT ( x int );

-- The table is truly private. 
-- It does not even exist in the the data dictionary, only your session 

-- By default, the moment you commit, the table ceases to exist 
commit;

select * from ORA$PTT_MY_TT;
-- ORA-00942: table or view does not exist

-- This can be changed by specifying that the definition should be preserved 
create private temporary table ORA$PTT_MY_TT ( x int )  
on commit preserve definition;

insert into ORA$PTT_MY_TT  
select rownum from dual  
connect by level <= 30;

commit;

select count(*) from ORA$PTT_MY_TT;
-- 30 

db<>fiddle demo

Answer 5

Additionally, Oracle (global) temp tables are very useful when each of your users/sessions need to each see a different set of data. Just INSERT the records to your global temp table and let Oracle manage keeping one user's set from another's, as well as the cleanup. You don't need to query them with the user's ID, a session id or whatever.

We find them very handy.

Answer 6

There is no temporary table, only global temporary table. The idea of a global temporary table is that the definition exists and can be seen by all, but data is private for each session. You can also configure if the data is cleaned upon commit or only when the session ends.