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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

syakobsonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.