Why are global temporary tables truncated when the connection is closed? 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.)
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.