Session ID of User

pts.
Tags:
SQL
I want to write a select statement in a database table trigger to see who is deleting a record (User, PC name, program). V$session provides the info but I do not know how to identify the session that is deleting. Please help. Oracle 8.0.5 on NT Server

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,
Use the following:

SELECT * FROM V$SESSION
WHERE AUDSID = USERENV(‘SESSIONID’);

This will return the record for the current database session.

Rgds,
Iudith

SELECT * FROM V$SESSION

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Boball
    How can we get the session ID for a SYS session? SELECT * FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID'); If I login as SYS from more than one terminal, the above query returns more than one row. AUDSID = 0, so in total, it returns about 6 to 8 background sessions (SIDs 1 to 8) plus the number of SYS logins. So how can I get my own SID for my SYS session? Thanks Bob
    0 pointsBadges:
    report
  • Welcome
    Hi Bob, You are right, in v$session there are several records with AUDSID = 0, belonging to user SYS or to oracle background processes. If you need however some unique identifier, then maybe you should take in this case not only session, but also some process information. I have no possibility to logon as SYS, so I cannot check how I could locate my own session among those having AUDSID=0. But for a regular session, for example if I logon to SQL*PLUS from my machine, then my own PC's number appears in the V$SESSION.MACHINE column. Maybe you could find the following select also useful: SELECT * FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR ; Also, there is a SQL function SYS_CONTEXT that could be helpful. For example, SYS_CONTEXT('USERENV','IP_ADDRESS') returns the IP address of the current session, and also many other context variables can be used as the second argument. I cannot check how this works for user SYS. Hope this could give you a hint. Best Rgds, Iudith
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following