Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

USERENV('sessionid') returns multiple rows

User_8P4FCNov 23 2012 — edited Nov 23 2012
hello
im trying to set up a db trigger that will log all DDL transactions performed on the db;
for that purpose the following statement is included in the trigger to return the information about the user making the changes:
	select 
      osuser,
      machine,
      process,
      program,
      AUDSID,
      sid,
      SERIAL#
   from 
      v$session
   where 
      AUDSID=USERENV('sessionid');
problem is, this statement is returning multiple rows when 2 users are connected to the same database using the same username e.g. SYS;
i can see that audsid is identical for these 2 users, but the osuser, machine etc are different, so i can easly say which one is which;
how to distinguish these in the query to be able to capture only the one actually executing the DDL transaction?
id appreciate any tips
thanks very much
rgds
This post has been answered by JohnWatson on Nov 23 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2012
Added on Nov 23 2012
6 comments
2,504 views