In-depth study of temporary tables in Oracle databases

Recently, considering the usage of temporary tables in the writing and Storage Processes on the Database End, we still have no idea what the Database End is doing? Whether the processing method is the same as that of SQL Server, and whether there are risks or other problems. In order to avoid unnecessary troubles in the future, I did in-depth research and checked some authoritative information.

Note the following for temporary Oracle tables:

The syntax for creating a temporary table is as follows:

CREATE GLOBAL TEMPORARY TABLE table
"(" column datatype [DEFAULT expr] [{ NULL | NOT NULL}]
[, column datatype [DEFAULT expr] [ {NULL | NOT NULL} ]... ")"
ON COMMIT {DELETE | PRESERVE } ROWS

There are two temporary tables in Oracle: Transaction-level temporary tables which automatically clear records at the end of the transaction, the other is session-level, which is automatically cleared after a session that we access the database ends. Multi-User parallel operation on temporary tables is not a problem. One session never stops another session from using temporary tables. Even if a temporary table is locked, a session does not prevent other sessions from using the temporary table.

If you have experience using temporary tables in SQL Server or Sybase, you should not execute select x, y, z into # temp from some_table to create and load temporary tables,:

1. For each database, create all temp tables as global temporary tables. This will be completed as part of the application installation, just like creating a permanent table.

2. Simply insert into temp (x, y, z) selelct x, y, z from some_table in the process.

The purpose is not to run the stored procedure to create a table. This is not the correct method in Oracle. DDL is a type of operation that consumes a lot of resources and should not be used during runtime. The temporary tables required by the application should be created during application installation, rather than during runtime. Temporary tables in Oracle are similar to temporary tables in other databases. You do not have to create a temporary table in each database. Temporary tables always exist. They exist as objects in the data dictionary and are always left empty until a session is put into the data.

All of them here show that if we create a temporary table in the stored procedure, each time we create a temporary table, our system will call this stored procedure with the user's operations, and there will be one more table at a time, we are unaware that the number of tables in the database will increase, but we do not know that there will be great risks, we must pay attention to this problem so that our systems can run smoothly and securely at the client. If you do not manually Drop a table, the temporary table still exists in the data dictionary.