Re: Trap SQL statements in network traffic instead of database

That's a possible solution if you understand that sampling != auditing (ie you may miss some statements).
If you decide that's an option, check first whether you are licensed for Diagnostic and Tuning Packs.
If so, just use built-in AWR/ASH which is basically what Mladen has described, just built in to the product (so minimal additional overhead). You might also look at AWR Warehouse to offload the historical data to a different instance.
If you don't have a license, then you may consider other open source implementations such as OraSash before rolling your own http://pioro.github.io/orasash/

Patrick

On 13 August 2017 at 05:32, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On Fri, 11 Aug 2017 14:43:12 -0600
> Sandra Becker <sbecker6925_at_gmail.com> wrote:
>
> > We need to produce a "log" of sql statements--along with the user, IP (or
> > host) they are coming from, and the sql statement--for another team to
> > analyze. My manager does not want to user auditing because of the
> > uncertainty of the load on this critical database. He suggested doing a
> > SPAM port capture. I opened a ticket with our SAs and they wanted to
> know
> > what ports. I gave them the listener ports. The SA ran a tcpdump (said
> it
> > was verbose), but it didn't give any information on users, app servers,
> or
> > sql statements. I really don't know what I'm doing here, just passing
> > information between my manager and SAs. So, questions:
> >
> > 1. Will tcpdump give me what my manager is asking for? If yes, what are
> > the options the SA should use?0
> > 2. Is there a better way to retrieve this information without using
> > database auditing?
> >
> > Any assistance you can provide will be greatly appreciated.
> >
> > --
> > Sandy B.
>
> Hi Sandy, this shouldn't be too difficult. All the user names, SQL_ID
> information and host names are in V$SESSION. Normally, this information is
> gathered by auditing. If you cannot use Oracle auditing, create your own.
> Write a script, preferably in Perl, which would gather the information from
> V$SESSION and V$SQL every second and write it into a normal table. That
> would not be completely exact result because it would miss some statements
> which last less than a second, but would be good enough. Don't forget to
> create a primary key on SID,SERIAL#,SQL_ID, so that you don't get thousands
> of records per single long lasting SQL. If the duration of the monitoring
> interval is a few hours, that shouldn't be a problem.
> Regards
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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