Database Reference
In-Depth Information
USERNAME SID SERIAL# SERVER PADDR STATUS PROGRAM
--------- ------ -------- --------- ---------------- -------- ---------------------
EODA 485 1105 SHARED 0000000071C4BE68 ACTIVE oracle@cs-xvm2 (S004)
From another terminal session, connect to the database as the user
SCOTT
(the
shared
text in the following
connection string maps to an entry in the
tnsnames.ora
file that instructs SQL*Plus to connect via a shared server
connection):
$ sqlplus scott/tiger@shared
SCOTT@ORA12CR1> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
From the original connection as
EODA
, run the query to show the
PROGRAM
information again:
EODA@ORA12CR1> select a.username, a.sid, a.serial#, a.server,
2 a.paddr, a.status, b.program
3 from v$session a left join v$process b
4 on (a.paddr = b.addr)
5 where a.username = 'EODA'
6 /
USERNAME SID SERIAL# SERVER PADDR STATUS PROGRAM
--------- ------ -------- --------- ---------------- -------- ---------------------
EODA 485 1105 SHARED 0000000071C478E8 ACTIVE oracle@cs-xvm2 (S000)
■
You need to use an account that has execute privileges on the
DBMS_LOCK
package. I granted my demo account
SCOtt execute privileges on the
DBMS_LOCK
package to accomplish this:
SYS@ORA12CR1> grant execute on dbms_lock
to scott;
Note
Notice that the first time I queried, I was using
S004
as the shared server. Then, in another session (as
SCOTT
),
I executed a long-running statement that monopolized the shared server, which just happened to be
S004
this time.
The first nonbusy shared server is the one that gets assigned to do the work, and in this case no one else was asking to
use the
S004
shared server, so the
DBMS_LOCK
command took it. When I queried again in the first SQL*Plus session,
I got assigned to another shared server process,
S000
, since the
S004
shared server was busy.
It is interesting to note that the parse of a query (returns no rows yet) could be processed by shared server
S000
,
the fetch of the first row by
S001
, the fetch of the second row by
S002
, and the closing of the cursor by
S003
. That is, an
individual statement might be processed bit by bit by many shared servers.
So, what we have seen in this section is that a connection—a physical pathway from a client to a database
instance—may have zero, one, or more sessions established on it. We have seen one use case of that when using
SQL*Plus's AUTOTRACE facility. Many other tools employ this ability as well. For example, Oracle Forms uses
multiple sessions on a single connection to implement its debugging facilities. The
n
-tier proxy authentication feature
of Oracle, used to provide end-to-end identification of users from the browser to the database, makes heavy use of the
concept of a single connection with multiple sessions, but each session would use a potentially different user account.
We have seen that sessions can use many processes over time, especially in a shared server environment. Also, if we
are using connection pooling with Oracle Net, then our session might not be associated with any process at all; the
client would drop the connection after an idle time and reestablish it transparently upon detecting activity.
In short, there is a many-to-many relationship between connections and sessions. However, the most common
case, the one most of us see day to day, is a one-to-one relationship between a dedicated server and a single session.
