Manage global temporary tables

Starting from version 11.0.1.0, you can create, identify and remove global temporary tables by running SQL commands.

Global temporary tables are like local temporary tables but they store schema in a catalog. Every SQL session that references a global temporary table causes a distinct instance of that global temporary table (i.e., a multiset of rows that is visible only to that SQL session) to be materialized. The multiset of rows that is referenced by the <table name> of a global temporary table cannot be shared between SQL sessions. The definition of a global temporary table appears in a schema. Users can access these global temporary tables from different sessions, but the user of one session cannot access data which is created by a user of another session. Also, if a user reconnects to database which has global temporary tables, they cannot access data from them.

When you enter a command to create a global temporary table, the system stores the table definition in the system catalog just as if a regular table was created. When a user enters any SQL command that accesses a global temporary table and the global temporary table has not been instantiated, the system will create a local temporary table which has the same name and schema as the global temporary table. The table will or will not be populated, depending on the WITH DATA or WITH NO DATA option used to create the global temporary table. A session that creates and uses a global temporary table will be the only session that can see the local temporary table object and data.