Re: Capturing all SQL_IDs for a SessionId
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx, darrah.john@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 26 Apr 2007 13:38:48 -0700 (PDT)
Trying to tie Audit Information to a UserId. The problem is that we use
Business Objects and the AUD$ contains a generic Id (let's say BO_USER ). So,
we don't know who the actual user is that accessed a particular table. It says
BO_USER access a table XYZ.
The workaround we have used is to have BO application pass the UserId as a
comment in the SQL statement itself, and that's working fine. SQL looks like
"SELECT /* USER('deepak') */ col_A, col_B etc.
As mentioned in an earlier post, I have already found a workaround. What I am
now doing is, as part of a logoff trigger, I capture the "SID, SERIAL#, AUDSID,
USER_ID" in another table for that session (joining v$session to v$open_cursor
and/or v$sql), where USER_ID is just a bunch of substr, instr to extract the
UserId from the SQL.
Once I have the above info, I can always join it to AUD$ and get the actual
user name that accessed a table.
-Deepak
----- Original Message ----
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
To: darrah.john@xxxxxxxxx; sharmakdeep_oracle@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Thursday, April 26, 2007 11:17:25 AM
Subject: Re: Capturing all SQL_IDs for a SessionId
dbms_monitor.session_trace_enable might work or fill the filesystem .
What problem are you trying to solve?
On 4/26/07, John Darrah <darrah.john@xxxxxxxxx> wrote:
> v$active_session_history is as close as you will get without putting a
> sql_trace on the session. It samples every second.
>
> On 4/25/07, Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx> wrote:
> >
> > Is there a way to capture all the SQL_IDs that a session generated -
> > historically?
> >
> > I have tried a couple of options like v$open_cursor (it's volatile in the
> > sense that entries exist as long as the session exist).
> >
> > v$active_session_history - Does only sampling and does not capture every
> > sql.
> >
> > Pls correct me if my above observations are wrong.
> >
> > Thanks,
> > Deepak
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
