Oracle global temporary table

The Tempotary table is used to save the intermediate result set during a transaction or session. The data saved in the temporary table is only visible to the current session, and data of other sessions is invisible to all sessions. Even if the current session has already submitted (commit) data, no data is visible to other sessions. For temporary tables, there is no multi-user concurrency problem, because one session does not block another session because a temporary table is used. Even if we "Lock" the temporary table, it will not prevent other sessions from using the temporary table. Temporary tables are much less redo than regular tables. However, because the temporary table must generate undo information for the data contained in the temporary table, a certain redo will also be generated. Update and delete generate the most undo, and insert and select generate the least undo. The temporary table www.2cto.com allocates storage space from the temporary tablespace of the currently logged-on user, or uses the temporary tablespace of the process owner if the temporary table is accessed from a definer permission process. A global temporary table is actually a template of the table. Creating a temporary table does not involve bucket allocation and does not involve the initial partition, which is different from that of a regular table. For a temporary table, a temporary segment is created for the session only when a session first puts data in the temporary table. Since each session will get its own temporary segment (rather than a section of an existing segment), each user may allocate space for it in a table in a different tablespace. The temporary tablespace of USER1 may be set to TEMP1, so its temporary table will be allocated from this tablespace. USER2 may use TEMP2 as its temporary tablespace and its temporary table will be allocated from it. Temporary tables in www.2cto.com Oracle are similar to temporary tables in other relational databases. The main difference is that temporary tables in Oracle are defined as "static. Each database only creates a temporary table once instead of every stored procedure in the database. Historical Oracle tables must exist. They are placed in the data dictionary as objects, but the temporary table always looks empty before the session is placed into the temporary table. Because temporary tables are statically defined, you can create views that reference temporary tables, create stored procedures, and reference temporary tables with only static SQL statements. The temporary table can be a session (the data in the temporary table can exist before submission, but the data does not exist after the connection is closed ), it can also be transaction-based (Data disappears after being committed ). Syntax: create global temporary table temp_table_session (...) on commit preserve rows is a session-based temporary table. These rows exist in this temporary table until the session is disconnected or before the row is physically deleted through a delete or truncate operation. Only my sessions can see these rows. Even if I have already submitted, other sessions cannot see my rows. Based on the transaction create global temporary table temp_table_session (...) on commit delete rows based on the transaction's temporary table, when my session is committed, the rows in the temporary table will disappear. You only need to return the temporary section allocated to this table. These rows will disappear and there is no overhead during the automatic clearing of the temporary table. Www.2cto.com temporary tables can have many attributes of permanent tables. They can have triggers, check constraints, indexes, and so on. However, some features of permanent tables are not supported in temporary tables, including the following features. There cannot be constraints on the integrity of the reference. A temporary table cannot be the target of a foreign key, nor can it be defined in a temporary table. IOT cannot be in any type of cluster. It cannot be partitioned. It cannot generate statistical information in all databases through the ANALYZE table command, one of the disadvantages of a temporary table is that the optimizer cannot normally obtain the real statistics of the temporary table. When the cost-based optimizer (CBO) is used, effective statistics are crucial to the success or failure of the optimizer.