Oracle单连接下多SQL命令的会话归属及全局临时表隔离可靠性问询

Let’s unpack your question step by step—there’s a key mix-up here between Oracle’s session/connection relationship and how global temporary tables (GTTs) handle data persistence.

First: Clarify Oracle’s Session vs. Connection #

You referenced the Oracle glossary definition, so let’s translate that to practical application behavior:

• For most standard application setups (like using .NET, JDBC, or ODBC drivers), a single database connection maps directly to one session. Unless you’re using advanced features like Oracle’s connection pooling with session multiplexing (rare in basic apps), every SQL command you run on that same connection shares the same session.

Your initial assumption that "each query is an independent session" is incorrect—that’s not how Oracle works by default. So why did your test return 0 rows?

The Real Culprit: Global Temporary Table Type & Transaction Behavior #

Oracle has two types of GTTs, defined by their ON COMMIT clause, which dictates when data is cleared:

1. ON COMMIT DELETE ROWS (transaction-private): Data is deleted as soon as the transaction commits. This is the default if you don’t specify the clause.
2. ON COMMIT PRESERVE ROWS (session-private): Data stays until the session ends (when you close the connection, or explicitly terminate the session).

In your test code:

• If your TMP_TABLE uses the default ON COMMIT DELETE ROWS, executing c1.Execute() likely triggered an automatic commit (most client drivers default to auto-commit for individual commands). The insert was committed immediately, so the GTT cleared the data before c2 ran.
• If you’d defined the GTT with ON COMMIT PRESERVE ROWS, c2 would have returned the 'TEST' value—because both commands share the same session.

Can You Depend on This Behavior? #

Yes, but with clear guardrails:

• Session/Connection Link: In standard setups, all commands on a single connection share one session. This is a reliable, documented behavior in Oracle.
• GTT Configuration: You must explicitly define your GTT with the right persistence rule. If you need data to persist across multiple commands in the same session, use ON COMMIT PRESERVE ROWS.
• Transaction Control: If using transaction-private GTTs, disable auto-commit and manage transactions manually (start a transaction, run insert + query, then commit/rollback) to keep data visible between commands.

Fixing Your Test #

To verify this, redefine your GTT explicitly:

CREATE GLOBAL TEMPORARY TABLE TMP_TABLE (FOO VARCHAR2(20)) 
ON COMMIT PRESERVE ROWS; -- Session-private, data stays until session ends

Then run your test code again (ensuring auto-commit is off if you’re using transaction-private tables). You’ll see c2 returns the 'TEST' value, confirming both commands are in the same session.

内容的提问来源于stack exchange，提问作者waldrumpus