Temporal tables in Oracle

Temporal tables in Oracle

In Oracle, a temporal table is "static", and it needs to be created one time, just like a normal data table, and its structure is valid from creation to deletion. In contrast to other types of tables, temporary tables allocate space only when the user actually adds data to the table, and the allocated space comes from the temporary table space. This avoids the contention for storage space with the persistent object's data.

The syntax for creating a temporary table is as follows:

CREATE GLOBAL TEMPORARY TABLE table_name(    column_name data_type,[column_name data_type,...])ON COMMIT DELETE|PRESERVE ROWS;

Because the data stored by the staging table is valid only during the current transaction or session
Therefore, temporary tables are divided into transaction-level temporary tables and session-level temporary tables.

transaction-level staging table

To create a transaction-level temporary table, you need to use the on COMMIT delete rows clause, and the records for the transaction-level staging table are automatically deleted after each commit transaction.

Example 1:

CREATE GLOBAL TEMPORARY TABLE tbl_user_transcation(       ID NUMBER,       uname VARCHAR2(10),       usex VARCHAR2(2),       ubirthday DATE)ON COMMIT DELETE ROWS;

session-level staging table

To create a session-level temporary table, you need to use the on COMMIT PRESERVE rows clause, and the record of the session-level staging table is automatically deleted after the user disconnects from the server.

Example 2:

CREATE GLOBAL TEMPORARY TABLE tbl_user_session(       ID NUMBER,       uname VARCHAR2(10),       usex VARCHAR2(2),       ubirthday DATE)ON COMMIT PRESERVE ROWS;

Operation Temp Table

Temporal tables in Oracle