Re: Capture SQL for selected users

"ghm" <ghm_at_dslextreme.com> wrote in message news:<10q04gv8apaa8a9_at_corp.supernews.com>...
> Is it possible without creating triggers on every table in my database to
> capture all DDL/DML (excluding SELECT) for selected users? For audit
> purposes, I need to capture the SQL for certain users only.
>
> Thanks in Advance

You could always issue a good ole' SQLTRACE on their session via a login trigger. You could then write their OSPID, logon time, username, etc to a table via the same trigger. This table could also have a BLOB column. On logoff, fire another trigger that loads their trace file (DBMS_LOB.LOADFROMFILE) into the BLOB in the row for their session.

If you ever need it, extract it from the database and run it through tkprof, or whatever. Although you didn't ask, you could also see the SELECT's they did issue. May be messy in any kind of "grouped" login type of application though, but it may work. Just another idea...

Regards,

Steve Received on Tue Nov 23 2004 - 10:42:26 CST