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@xxxxxxxxx> wrote:
On Fri, 11 Aug 2017 14:43:12 -0600
Sandra Becker <sbecker6925@xxxxxxxxx> wrote:
We need to produce a "log" of sql statements--along with the user, IP (orknow
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
what ports. I gave them the listener ports. The SA ran a tcpdump (saidit
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
--
//www.freelists.org/webpage/oracle-l
