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