USERENV('SESSIONID')

I need a number to identified and Oracle session (active, inactive or killed) to use inside a program.
I think that the column AUDSID from view V$SESSION is the correct number for that propose.

My program can not access V$SESSION (because it has not the privilege), so I use the value returned from USERENV('SESSIONID').
But I have some questions:

1. USERENV needs the AUDIT TRAIL with the TRUE value. In our databases, the AUDIT RAIL is FALSE, but the USERENV('SESSIONID') function functions property. Why?

2. When is this number reseated to zero?

3. Do you know another alternative different than AUDSID?

Thanks in advance.

Juan Pablo.-
jpussacqAsked:
Who is Participating?
 
syakobsonConnect With a Mentor Commented:
Juan,

AUDSID is assigned 1 THE VERY FIRST TIME you connect to the database. It is not reset when you shutdown and restart the database. Let me explain more detailed how AUDSID works. It is simply implemented via Oracle sequence SYS.AUDSES$ which has MIN_VALUE=1, MAX_VALUE=2,000,000,000 and is incremented by 1. It also has CYCLE_FLAG=Y and CACHE=20. So theoretically you can say AUDSID is not unique in general since if session with AUDSID=1 will stay connected and you never shutdown your database eventually sequence AUDSES$ will reach 2,000,000,000 and cycle back to 1. Then you will have two sessions with AUDSID=1 and mess up auditing. So theoretically AUDSID is not unique at all. However, it would take about 64 years for AUDSID to reach 2,000,000,000 creatingh one session every second. So when I wrote AUDSID is unique from  database startup to shutdown I assumed database will be restarted at least once in 60 years and sessions with low AUDSID will disappear. Anyway, as you can see, you can safely assume AUDSID is unique.

Solomon Yakobson.
0
 
Mark GeerlingsDatabase AdministratorCommented:
If v$session has the info you want, you could connect as sys, and either create a public synonym for v$session and grant select on v$session to public or create new view that is a select of the columns you want from v$session, and create a public synonym for that and grant select on that view to public.
0
 
syakobsonCommented:
1. Every session is given audit session id regardless of audit trail being true or false. Audit session id is a unique number generated at connect time. It is then stored in V$SESSION (column AUDSID). USERENV('SESSIONID')  simply returns that number from V$SESSION without you needing access to it. As you can see, there is no difference between USERENV('SESSIONID') and session AUDSID from V$SESSION.

2. Audit session id along with session id never change through the whole session life cycle.

3. I am not aware of any other way than AUDSID to uniquely identify a session between database startup and shutdown. Session id (SID) is only unique among current connections, but will be evetually reused after session disconnects. AUDSID is never reused and therefore is unique from database startup to shutdown. However, keep in mind that AUDSID is not unique between the instances (in case you are on parallel server).

Solomon Yakobson.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
jpussacqAuthor Commented:
Thanks Solomon Yakobson.
But I have two more questions:

Is "1" the first value assigned to AUDIT_SID each time you startup the database?
Is there a maximum value for AUDIT_SID? What happens when AUDIT_SID reaches this value?

Juan Pablo.-
0
 
syakobsonCommented:
Sorry, I forgot one very important moment. Sessions connected INTERNAL, AS SYSDBA and AS SYSOPERT always have AUDSID=0.

Solomon Yakobson.
0
 
syakobsonCommented:
Ooops, I meant AS SYSOPER.

Solomon Yakobson.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.