1

I'm a relatively new Oracle database developer coming from SQL Server.

I noticed that global temporary tables (created with ON COMMIT PRESERVE ROWS) lose their contents when the session times out, but the tables themselves still exist and have to be manually dropped before rerunning whatever script I have that creates them.

In SQL Server, a temporary table (which isn't global in any sense) is dropped and completely gone when the session is closed.

What is the logical or technical reason for Oracle keeping temporary tables around - but truncating them - when the session is closed?

(If there is a database or server option to avoid this behavior, it is irrelevant to my use-case since I'm only a developer and will be creating scripts that are run on an outside database.)

1

Column names must be known at compile time.

You can't create a table in a pl/sql block and then use it in the same pl/sql block. This even applies to oracle-18c private temporary tables.

As such, you create a Global Temporary Table (GTT) once and re-use it throughout the applications life.

Also, the data in GTTs are private to the session. Let's say there exist a GTT called TEMP_ID_LIST. Your session won't see my data in TEMP_ID_LIST. My session won't see your data in TEMP_ID_LIST.

Oracle-Base URLs for reference

1
  • When connection is closed, the transaction is rolled back, that's why data is not preserved.
  • The rule of thumb for Oracle is "never use DDLs for business logic", you should never do any DDL during normal business run.

If you drop a TABLE (persistent or temporary) "all" the PL/SQL code gets invalidated and has to be re-compiled again.

GTT are called "global" because their definition are visible to all sessions.

UPDATE: GTTs are used sporadically in Oracle. Usually when you use a temporary table in MS SQL, you use a cursor in Oracle. IMHO in most cases you can avoid using them. GTTs are used for example as staging storage for some ETL loaders, so their stucture is predefined/fixed.

  • > GTT are called "global" because their definition are visible to all sessions. What is the purpose of this, allowing you to create tables that are visible to others but data that is private? Is this mainly for web applications? (to share a single table for a specific type of entity, but have per-session data for each connected user) – Z. Cochrane Sep 4 '18 at 15:58
  • 1
    "When connection is closed, the transaction is rolled back, that's why data is not preserved." I cannot follow this argumentation. This data may be already committed ("ON COMMIT PRESERVE ROWS"). I think the reason is simply that if it would be preserved after the session ends then nothing is temporary at all. – miracle173 Sep 4 '18 at 16:00
  • @zabeus please add such questions to your post and don't hide them in comments. – miracle173 Sep 4 '18 at 16:07
0

but the tables themselves still exist and have to be manually dropped before rerunning whatever script I have that creates them

So you periodically ran a script that creates a table and at the end drops this table? Isn't this a case where it is evident that it makes sense to keep the table and erase only the content, so that you avoid this create table - drop table -create table sequence?

This kind of tables are not for ad hoc queries that will be executed only once, but for jobs that will be repeated and where maybe a lot of these jobs ran concurrently. In these cases you may save resources when using these kind of temporary tables. And you can use it in your programs without using special tricks that you need when the table does not exist at compile time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.