An in-depth study of interim tables in Oracle

oracle| temporary table
We have recently taken into account the fact that our database-side write stored procedures are used in temporary tables because we



It is not clear how the database end is handled, and how SQL Server is handled



The same, whether there will be hidden dangers and so on some issues, in order to avoid future unnecessary trouble I did a deep



Into the research and view some authoritative information, now and everyone to share, I hope everyone in the processing



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's at the end of the transaction since



The dynamic emptying of the record, the other is session-level it after we access the database is a session is over



Automatic emptying. About temporary tables multiuser parallelism is not a problem, a session never blocks another



A session uses a temporary table. Even if you lock a temporary table, a session will not block other



Use the temporary table of their words.



If you have experience with using temporary tables in SQL SERVER and \ or Sybase, you need to primarily test



Consider not executing select X,y, z into #temp from some_table to create and mount temporary



Table, but instead:



1. For each database, create all the temp tables as global temporary tables. This will make



Complete 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. Doing this in Oracle



Not the right way. DDL is a very large resource consuming operation, and try not to



Use, the temporary tables that the application needs should be created during application installation, not at run time



Create. Temporary tables in Oracle are similar to temporary tables in other databases, and in each database



You do not have to create a temporary table 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 in which the data is placed.



All I'm here to say is that if we build a temporary table in a stored procedure, each time we create a



My system calls this stored procedure with the user's action, and one more such table at a time, we



Unknowingly the number of tables in the database will be more and more, and we do not know that there will be a large



The hidden dangers of all said this can not be belittled, in order to our system in the customer there is smooth, safe



The operation we must pay attention to such a problem. If you do not manually drop the table, the temporary table still exists in the data dictionary.