Re: os users, sessions, and connections.
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 9 Feb 2006 11:54:56 +0000 (UTC)
"DaLoverhino" <DaLoveRhino@xxxxxxxxxxx> wrote in message
news:1139274175.910349.269400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I've been reading about the difference between connections and sessions
on Kyte's site and this group. It's a bit rough for me, so I got a few
questions. I'm also having a hard time figuring out how it could be
applied to auditing when the OS user uses an app to do database work,
and the app may have 'connection pooling'.
Here's a few items:
1. From what I've read a session is a logged connection. Does that mean
there's no such thing as a connectionless session?
2. Can you have one connection to multiple sessions?
3. If an application has (for example) one connection to the database
but has two users using the app to do DB work, what does the app do?
Assume that each user is doing his work as if simultaneously (not
taking turns to make changes.) Does the program open up two sessions
and switch between the two?
4. If only one user is used by the app to do the db changes of many OS
users, how would I know which OS user did what?
Plus, if you have a book you know that really points this stuff out,
that would be great too.
thanks.
A program can create a process that attaches to the Oracle
instance, this is a connection and appears in v$process.
The same program can then use that one process to
carry several sessions - each one appears in v$session.
The simplest way to see this is to use SQL*Plus to
connect to an account that can query v$session, and
do the following (paddr is the address of the process
through which a session is connected).
select sid, serial#, paddr, username from v$session
where username = {me}
set autotrace on
select sid, serial#, paddr, username from v$session
where username = {me}
set autotrace off
An example results I got from running this as 'SYS' are:
SQL> select sid, serial#, paddr, username from v$session where username =
'SYS';
SID SERIAL# PADDR USERNAME
---------- ---------- -------- ------------------------------
10 44880 6C23FCCC SYS
1 row selected.
SQL> set autotrace on
SQL> select sid, serial#, paddr, username from v$session where username =
'SYS';
SID SERIAL# PADDR USERNAME
---------- ---------- -------- ------------------------------
9 4168 6C23FCCC SYS
10 44880 6C23FCCC SYS
2 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FIXED TABLE (FULL) OF 'X$KSUSE'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
591 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> set autotrace off
SQL>
As you can see, SQL*Plus implements autotrace by
creating a second session (that connects as SYS) to
query the session stats for the first session. The second
row comes into existence when you enable autotrace,
and uses the same process (paddr) as the first.
As far as auditing, triggers, context etc. are concerned,
the second session is isolated from the first, and operates
under the account name reported. (In my case SYS).
Part of your confusion probably lies in the fact that there
are two mechanisms that are called 'connection pooling'.
one of them is the above - a single connection is shared
by many sessions (like car-pooling). This is useful for
an application operating over an INTRA-net, where you
have a known client base, and each individual has a
database account that can be identified.
However, very few people do this. The commoner
interpretation of 'connection pooling' is used to describe
how a program creates many connections, with a single
session operating on each connection, all sessions using
an 'application account'. An incoming request is simply
routed to whichever connection/session happens to be
free. In this context, auditing etc. is the application's
problem - the database doesn't know that all the different
queries are coming from different individuals; everything
is operating under a single database account.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 2nd Feb 2006
.
- References:
- os users, sessions, and connections.
- From: DaLoverhino
- os users, sessions, and connections.
- Prev by Date: Re: Creating a corrupt block(s)
- Next by Date: Re: ORACLE on Linux - IO bottleneck
- Previous by thread: Re: os users, sessions, and connections.
- Next by thread: tkprof explain=user/password password exposed?
- Index(es):
Relevant Pages
|
