Global Temporary Tables in the Oracle Database
- killiansbytes
- May 8
- 3 min read
Today's throwback feature of the week is the Global Temporary Table (GTT). Global Temporary Tables are permanent database objects stored on disk but the content is visible only to the session that inserts the data. The content of the GTTs can exist for a single transaction, or across the lifetime of a session.
Using Global Temporary Tables
I'll start with an example. You've got an e-commerce site that has periodic 'flash sales' and you want to create reports of user behavior during the sales, but you don't want to store the users actions permanently in the database.
You think "I'll use Global Temporary Tables", so you create the table like:
CREATE GLOBAL TEMPORARY TABLE user_activity (
user_id NUMBER,
activity VARCHAR2(100)
)
ON COMMIT DELETE ROWS;Here the 'ON COMMIT DELETE ROWS;' is optional, and is the default for GTTs. This tells the database to only keep data for this transaction.
Now you add the users activities during the sale:
(NOTE: this syntax uses the updated table value constructors in Oracle Database 23ai. If you're trying this on a version prior to 23ai, you will need to add an insert statement for each row).
-- Insert user activities during the flash sale
INSERT INTO user_activity VALUES
(101, 'Added to cart'),
(102, 'Checked out'),
(103, 'Viewed item');
-- Quick check
SELECT * FROM user_activity;
Once the transaction ends, the data is no longer persisted.
COMMIT;
SELECT * FROM user_activity;
Global Temporary Tables also allow data to persist through transactions. Here the 'ON COMMIT PRESERVE ROWS;' means the table's contents will last until the current session ends.
DROP TABLE user_activity PURGE;
CREATE GLOBAL TEMPORARY TABLE user_activity (
user_id NUMBER,
activity VARCHAR2(100)
)
ON COMMIT PRESERVE ROWS;
-- Insert user activities during the flash sale
INSERT INTO user_activity VALUES
(101, 'Added to cart'),
(102, 'Checked out'),
(103, 'Viewed item');
COMMIT;
SELECT * FROM user_activity;
Global Temporary Tables Across Sessions
Since the GTTs exist on disk as database objects, other sessions can use them. However, the contents of the GTTs are only visible to that specific session.
In the photo below, the session on the left (session 1) is the example we just looked at above. The session on the right is a new session, that can use the global temp table we created but doesn't see session 1's data.
How does this differ from Private Temporary Tables?
In a previous post, I wrote about Private Temporary Tables. Although similar, these two features have slightly different purposes:
Global Temporary Tables (GTTs): GTTs store the table definitions on disk and are available for all sessions to use. This is good when you need shared table definitions across multiple sessions but session-private data. The data in the global temp tables is only visible to the session that created it.
Private Temporary Tables (PTTs): PTTs only store the table definition in memory and are only visible to the session that created the table. Here, both the table and the associated data are private to the session and are automatically dropped at the end of the session or a transaction.
A couple things to note:
When creating data in the database, the database tracks the Undo. This is the same for both the Global and Private temporary tables. In version +12c, there is a parameter 'TEMP_UNDO_ENABLED' that lets you use a temporary undo log. This improves performance by reducing both the undo and redo generation.
By default, the temporary undo is turned off. If you're consistently using these features you should use the temporary undo. You can set the parameters to true with the following:
ALTER [SESSION or SYSTEM] SET TEMP_UNDO_ENABLED = TRUE;
ALTER [SESSION or SYSTEM] SET TEMP_UNDO_ENABLED = FALSE;Indexes can be created on global temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
More info:
-Killian
Comments