Introduction to the Oracle temp table

Introduction to the Oracle temp table

[Preface] Today, the developer encountered a problem where data could not be found in the temp table. After learning a little bit about it, I feel that there are still many knowledge points, so I will sort them out for your reference;

[Overview] Not all data is permanently stored in the database during business implementation. Some businesses store data through temporary tables or intermediate tables during implementation, after the business is completed, the data in the temporary or intermediate table can be deleted. Based on this business feature, Oracle's global temporary table satisfies this business function;

[Note] Some developers will create an entity table to temporarily store data. After the business process is completed, they will drop the entity table to implement the temp table function;

[Details]

Concept Description: the data in the temporary table is only valid for the current Session. Each Session has its own temporary data and cannot access the data in the temporary table of other sessions. Therefore, DML locks are not required for temporary tables.

When a session ends (the user Exits normally and the user does not exit normally and the ORACLE instance crashes) or a transaction ends, Oracle executes the TRUNCATE Statement on the table in the session to clear the temporary table data.

However, data in other temporary tables in the session will not be cleared. You can index the temporary table and create a view based on the temporary table. Similarly, indexes created on temporary tables are temporary and only valid for the current session or transaction.

Category: Oracle temporary tables are divided into session-level temporary tables and transaction-level temporary tables.

Session-level temporary table: the data in the temporary table only exists in the session lifecycle. When the user exits the session, Oracle automatically clears the data in the temporary table.

Transaction-level temporary table: the data in the temporary table only exists in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

[Create script]

On commit delete rows: Method for creating a transaction-level temporary table

Script:

Create global temporary table TEST1

(Id number)

On commit delete rows;

On commit preserve rows: Method for creating a session-level temporary table

Script:

Create global temporary table TEST3

(Id number)

On commit preserve rows;

Lab operation]

1. Create global temporary table TEST1, insert data 1, and view data in session1;

For global temporary tables, sessions are isolated;

Ii. Test ON COMMIT DELETE ROWS

Data only exists in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

Iii. Test the on commit preserve rows session-level temporary table

The inserted data can be queried after the COMMIT operation. Although the transaction ends, the session continues to exist;

Log out and log on again. The data in the table has been truncate, because the session has ended. This is a session-level temporary table;

[Conclusion] The temp table of oracle can meet the problem of temporary data storage during actual development. Understanding is to make better use of the technology and understand the two settings of the temporary table, which is of great help to the actual work;