In OceanBase database's Oracle mode, temporary tables are used to store data that exists only for the duration of a transaction or session.
Overview of global temporary tables
Global temporary tables are designed to hold data for a limited period, which can be either the lifetime of a transaction or a session.
While the column definitions of a global temporary table are visible to all sessions, the actual data in the table is private to each session. This means each session can only see and modify its own data.
For example, in a student course selection scenario, a temporary table can be used to temporarily store the courses selected by a student. During the session, this data is private to that student. When the student finalizes their course selection, the application moves the information from the temporary table to a regular table. After the session ends, the database automatically deletes the data from the global temporary table.
How to create global temporary tables
You can create a global temporary table using the CREATE GLOBAL TEMPORARY TABLE statement. The ON COMMIT clause controls whether the table is transaction-based or session-based:
ON COMMIT DELETE ROWS: Creates a transaction-level temporary table; data is deleted when the transaction is committed.ON COMMIT PRESERVE ROWS: Creates a session-level temporary table; data is deleted when the session ends.
You can also create indexes on global temporary tables. The data in these indexes is temporary and private to each session, just like the data in the table itself.
Temporary external tables
A temporary external table is an external table that is created temporarily during a query to read data. Once the query is complete, the table is automatically deleted.
Starting from OceanBase Database version V4.3.5 BP1, you can use the SELECT URL and LOAD DATA statements to read or import data from external tables.