Re: Session variables and PL/SQL

Sorry I did not specify the problem clearly enough : - the job is to create triggers which perform audit functions

• when, where did this user logon
• when, where did he logoff
• what did he do inbetween under the premise: the same user can log on multiple times, from different PC's, with different apllications ....

My option seems to be :
Create a logon trigger with

-select * from v$session where user#=(select uid from dual)   and status='ACTIVE'
(we hope the others are not active just right now) The SID in v$session has a value for example 7.

-select userenv('SESSIONID') as MY_SID from dual The USERENV('SESSIONID') has a value '735'.

-insert into MY_LOGON_LOGTABLE ... MY_SID, LOGIN_DATE etc.

Then use this table in my triggers to record :  who changed what, from where, in which session, ...

But is there an easier way to find the SESSIONID associated with the UID / USERENV or whatever ?   

Ted

On Sat, 19 Jul 2003 11:08:45 -0700, "Anna C. Dent" <anacedent_at_hotmail.com> wrote:

>Ted Knijff wrote:
>> Hi,
>>
>> how can I best save and retrieve variables based on a session ?
>>
>> The problem :
>> -a user logs in multiple times from different clients / from the same
>> client PC (i.e. multiple sessions, same user)
>> - I want to save the session parameters (login time etc. - as in
>> v$session view) and access these variables from PL/SQL
>
>So what is stopping "you" from doing so?
>
>> - do I have to save his data in a table and retrieve them every time ?
>
>I?
>as in from a separate session?
>as in from a LOGON trigger?
>
>"every time"?
>Every time there is a blue moon?
>
>> - how can I best access the session-id to uniquely identify the
>> session (performance) ?
>
>The Session ID (SID) is unique between database bounces.
>What is preventing you from using the SID from V$SESSION?
>

EMail: knijff_at_bigfoot.com Received on Sun Jul 20 2003 - 04:27:06 CDT