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

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 3

Nice to know: One can create indexes on global temporary tables in Oracle.

Answer 4

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 5

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.