What are the difference between local table and global table?

Question

Hi What are the difference between local table and global table? Karthika Palaniswamy

Gaurish Kumar · Answer

Hi A local temporary table exists only for the duration of a connection or if defined inside a compound statement for the duration of the compound statement. A global temporary table remains in the database permanently but the rows exist only within a given connection. When connection is closed the data in the global temporary table disappears. However the table definition remains with the database for access when database is opened next time.

Kirtan Patel · Answer

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. check  Do you like this answer  if it helps you :)