What is the lifecycle of a Oracle SQL session?

What is the lifecycle of a Oracle SQL session?

A session is a logical entity that exists from the time your application code connects to the database to the time it disconnects.

Sessions are independent of the physical (resource-based) entities that support them, such as connections, server processes, network connections, etc.

In the simplest (and most common, imo) configurations (i.e., "dedicated server"), there are 1-1 relationships between the logical sessions and the physical connections and server processes. In more advanced configurations to support large numbers of users, physical resources (connections, processes, network resources) may be shared by/multiplexed among multiple sessions.

I have run the above in my application, and as expected, the command c2 returned zero results. I take this to mean that each query constitutes its own session

Unless your application code is disconnecting from or closing/releasing its connection between calls, this is almost certainly not the case.

Global temporary tables can be created with two options: ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS.

The first will cause all GTT data to removed upon a commit or rollback. The second will cause all GTT data to persist for the entire session.

If you are NOT using ON COMMIT DELETE ROWS, then you should not assume the GTT is empty at the start of a given call. If you require the GTT to be empty at the start of the a procedure, you must DELETE FROM your GTT (or TRUNCATE it) at the beginning of the procedure (or the very end).

Note that even with this restriction (i.e., having to handle emptying out the GTT yourself within a session), global temporary tables are still useful because they still protect one session from seeing another session's data. They also write less redo than regular tables, especially in version 12.1 and later. In fact, as of Oracle 12.1, they need not write any redo at all, making them useful in read-only and standby databases.

Aside...

I have run the above in my application, and as expected, the command c2 returned zero results

For this to make sense, your GTT must have been created with ON COMMIT DELETE ROWS and your application code has some kind of "autocommit" feature enabled where it is automatically committing after each Command.Execute(). Or else you're misinterpreting/misreporting your test results.

I don't know whether your application maintains an Oracle session between calls or not, but either way you need to beware of assuming the global temporary table (GTT) will be empty on the second call. Because if it gets a "new" session each time, what that probably really means is you are picking up a free session from a connection pool. And the previous user of that session (which may be you) may have already populated the GTT for that session. I have found this to be the case when using Oracle Application Express (APEX), which is an HTTP-based application framework.

You would need to call an Oracle stored procedure that used the GTT and then truncated (emptied) it (and/or truncated it before using it) to be sure of getting a clean slate each time.