Oracle development topic: temporary tables

What is the difference between a temporary table and a formal table?
What is the role of a temporary table?
How can I create a temporary table?

 

 

Temporary tables are generally stored in temporary tablespaces.
There are two types: Transaction Level and session level.
It is generally used to store temporary data.
Transaction-level temporary tables are automatically deleted after the transaction is committed, and session-level temporary tables are deleted after the session ends.
Session level
Create global temporary table tablename (col1 coltype,...) on commit preserve rows;
Transaction Level
... (Same as session level)... on commit Delete rows;

 

When creating a data table, if there is no special description, the table we create is a permanent relational table, that is, the corresponding data in this table, unless it is the deletion we show, the data in the table will always exist. 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 ));

I suggest:

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.

(2) analysis of changes in 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 to say, 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.

3. Notes for temporary table management.

A temporary table is a special table structure compared with other tables, but it has a great role. If the Oracle database does not have such a table, it is really not good. To manage such special tables, we need to pay attention to several details.

First, note that temporary tables cannot store data permanently. It is called a temporary table because the content in the table only exists temporarily. When a session or transaction ends, the content in the table is automatically cleared. Therefore, do not store permanent data in temporary tables. In practice, some people prefer to put the test data in the temporary data table when testing the database. In fact, this is an error in understanding the temporary ORACLE data table. If we put the data to be tested in the database, such as the sales order content, in the temporary table of the database, in other functions, such as to test the function of the sales order daily report, the relevant order content cannot be found. The content in the temporary table does not exist because it leaves a specific session or transaction. Therefore, the temporary tables mentioned in the oralce database are not for us to store test data.

Second, the data in the temporary table will not be backed up or restored, and there will be no log information for its modifications. If some information is stored in the temporary table of the database during database operations. The server suddenly becomes a machine. At this point, we want to restore the contents of the temporary database table through the database backup file, or view the log information of the temporary table, it is impossible to achieve. In other words, when the server is restarted, the contents in the temporary table will be cleared. Anywhere in the database, such as the database backup file or log information, you cannot find what is saved in the temporary database table before the restart, it seems that the temporary table is not operated at all.

Third, manage the temporary table space. A temporary table is also a type of table in the Oracle database. It also has a corresponding tablespace. When creating a temporary table, if we do not specify a tablespace, the default tablespace is system. For tablespace management of temporary tables, we need to pay attention to a small detail. If we attribute the tablespace of a temporary table to system, that is to say, the default tablespace cannot be deleted when a specific tablespace is not specified during the creation of a temporary table. If we specify a tablespace other than system when creating a temporary table space, we can delete the tablespace when we do not need it. Therefore, to facilitate subsequent management, we recommend that you specify a tablespace when creating a temporary table.

Fourth, pay attention to a problem. The temporary table only has temporary data, and the table is still permanent. That is to say, when a session ends or a transaction is completed, the data in the temporary table is deleted, but the temporary table still exists. That is to say. The temporary table in the Oracle database is global, but the data is temporary. This is quite different from the SQL Server database system. In fact, the two databases differ greatly in the processing of temporary tables, each having its own characteristics. In the futureArticle.

Fifth, note that the Oracle database does not lock the corresponding records when entering data in the temporary table. That is to say, when a DML statement is executed on a temporary table, the record is not locked or the changed data content is written to the redo log. Therefore, you cannot use a temporary table to store permanent data or perform common operations on the temporary table. This is a problem that beginners often encounter when managing temporary database tables.

Sixth, temporary tables and common tables cannot be converted to each other. Generally, after a temporary table is created, it cannot be converted to a permanent table. Therefore, this also illustrates the fact that it is inappropriate to use a temporary table as a test table during database design. This temporary table may be wrong with the temporary table we understand literally, not the temporary table we think is created to test the table structure. This is an error that we often make when getting started with Oracle databases.