Posted on 1999-08-09
Medium Priority
Last Modified: 2012-06-21
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.-
Question by:jpussacq
  • 4
LVL 35

Expert Comment

by:Mark Geerlings
ID: 1088176
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.

Expert Comment

ID: 1088177
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.

Author Comment

ID: 1088178
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.-
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.


Accepted Solution

syakobson earned 280 total points
ID: 1088179

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.

Expert Comment

ID: 1088180
Sorry, I forgot one very important moment. Sessions connected INTERNAL, AS SYSDBA and AS SYSOPERT always have AUDSID=0.

Solomon Yakobson.

Expert Comment

ID: 1088181
Ooops, I meant AS SYSOPER.

Solomon Yakobson.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question