What is the difference between a temporary table vs global temporary table in Oracle?
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
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.