Two temporary tables for Oracle databases

Two temporary tables in the Oracle database. If we do not specify the data table when creating the data table, the table we create is a permanent relational table, that is, the corresponding data in this table will always exist unless it is deleted. Correspondingly, there is also a type of table in the Oracle database, called a temporary table. The biggest difference between a temporary table and a permanent table is that the data in the temporary table will not exist forever. When a session ends or the transaction ends, the data in this temporary table is automatically cleared by the database without being deleted by the user. 1. Manage temporary transaction tables. (1) create a temporary transaction table. Oracle databases can be divided into temporary transaction tables and session temporary tables based on the nature of temporary tables. A temporary transaction table is an index that is valid only in the current transaction. Generally, when creating a data table, if the table is not specified as a session temporary table, the table is a transaction temporary table by default. We can use the following statement to create a temporary transaction table. Create global temporary table Temp_user (id number (12) Primary key, name varchar2 (10); In the statement for creating a temporary table, although it is not explicitly indicated that the table is a temporary transaction table, by default, if it is not specified, the system defaults to a temporary transaction table. When creating a temporary transaction table, you can leave the keyword unspecified. However, this is troublesome. I suggest you use specific keywords to explicitly specify any temporary table you are creating, which is convenient for everyone. Generally, the on commit delete rows keyword can be used to indicate that the table is a transactional temporary table, rather than a session temporary table. (Note: DDL automatically and implicitly commits transactions. Therefore, you must pay attention to the use of temporary tables in procedure.) (2) analyze the changes of temporary transaction table data. When the transaction ends, the temporary transaction table is cleared. Therefore, after we insert data into the temporary database table, the data in the table will exist as long as the transaction is not committed. However, after the transaction is committed, the data in the table will be deleted. In addition, this change is not displayed in the redo log. What is the difference between a specific transaction temporary table and a session temporary table? We will introduce the session temporary table in detail. 2. Manage session temporary tables. The session temporary table, as the name implies, is a temporary table that is only valid in the current session. After the current session is closed or a new connection is established, the content in the data table is cleared. What is the difference between a session temporary table and a transaction temporary table? Let's look at the differences in an instance. (1) first, create a session temporary table. Create global tempopary table TEMP_USER (id number (12) Primary key, name varchar2 (10) on commit preserve rows; that is, the syntax for creating a session temporary TABLE is roughly the same as that for creating a transaction temporary TABLE, only the last keyword is different. Although the two tables are similar, their internal processing mechanisms are quite different. (2) Insert data into the table. Insert into TEMP_USER values (1001, 'Victor '); the method for inserting data into a temporary database table is the same as that for inserting data into a common table, all operations are performed using the insert into statement. The data in this temporary table exists in this table before the session ends. (3) Submit the transaction and query related records. After the transaction is committed using the COMMIT statement, the SELECT query statement is used for query. We know that if the table is a temporary transaction table, the contents of the table will be deleted after the transaction ends. However, this is a temporary session table, so even if the transaction is committed, the employee record can still be found when the SELECT statement is used for query. (4) end the current session and reconnect to the database. When closing the current session and re-connecting to the database and querying with the SELECT statement, what will happen? At this point, we cannot find the data we just inserted. That is to say, when the conversation is closed, the database system has deleted the original data. From the above analysis, we can see that the main difference between the session temporary table and the transaction temporary table is that the data deletion time is different. A transactional temporary table clears data when the transaction is committed, while a session temporary table clears the temporary table when the current session is closed. As long as the current session is not closed, even if the transaction is completed, the data in the temporary session table still exists and will not be cleared.