Re: Capturing all SQL_IDs for a SessionId

If the session closes the cursor before logging off, you will miss those statements. I think v$active_Session_history and dba_hist_active_Sess_history are what you want here. I don't know much about auditing does anyone know if it collects information on the user and proxy user if someone connects through another user? If it does, that may be another solution.

On 4/26/07, Deepak Sharma <sharmakdeep_oracle_at_yahoo.com> wrote:
>
> 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_at_gmail.com>
> To: darrah.john_at_gmail.com; sharmakdeep_oracle_at_yahoo.com;
> oracle-l_at_freelists.org
> 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_at_gmail.com> 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_at_yahoo.com> 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
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> > >
> > >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
> --
> http://www.freelists.org/webpage/oracle-l
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

--
http://www.freelists.org/webpage/oracle-l