In-depth study of temporary tables in Oracle databases

We have recently taken into account the fact that our database-side write stored procedures are used in temporary tables, as we are not yet clear on what the database end is dealing with? Is the same way as SQL Server, there are some problems, and so on, in order to avoid unnecessary trouble in the future I did in-depth research and review some authoritative information.

Oracle Temp table is to note:

The first is the syntax for creating temporary tables:

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 types of temporary tables in Oracle that are temporary tables at the transaction level. It automatically empties records at the end of the transaction, and the other is the session-level automatic emptying after the end of a session we're accessing the database. About temporary tables multiuser parallelism is not a problem, and one session never prevents another session from using a temporary table. Even if you lock a temporary table, a session does not prevent other sessions from using their temporary tables.

If you have experience using temporary tables in SQL Server or Sybase, the main consideration is not to execute select X,y, z into #temp from some_table to create and load temporary tables, but instead:

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

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

Just to understand this, the purpose here is not to run a stored procedure to create a table. This is not the right way to do this in Oracle. DDL is an extremely resource-intensive operation that is not used at run time, and temporary tables that the application needs should be created at the time of application installation, not at run time. Temporary tables in Oracle are similar to temporary tables in other databases, creating temporary tables once in each database without having to be created once in each stored procedure in the database. Temporary tables always exist, they exist as objects in the data dictionary, and always remain empty until there is a session where the data is placed.

All I'm here to explain is that if we build temporary tables in the stored procedure, every time I build one then my system calls this stored procedure with the user's operation, each time more than one such table, we unknowingly in the database the number of tables will be more and more, and we do not know that there will be a lot of hidden dangers of all said that this can not be belittled, We must pay attention to this problem in order that our system can operate smoothly and safely in our customers. If you do not manually drop the table, the temporary table still exists in the data dictionary.