Re: Capturing all SQL_IDs for a SessionId

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
> > --
> > 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

Other related posts: