Re: Trap SQL statements in network traffic instead of database

From: Jonathan Lewis <>
Date: Mon, 14 Aug 2017 11:25:30 +0000
Message-ID: <MMXP123MB1037ABFE422B3CF3E28A8084A58C0_at_MMXP123MB1037.GBRP123.PROD.OUTLOOK.COM>

The difficulty with that one is that to meet the stated requirement the system tap code will have to be modified trap on a call to execute, and then find the SQL text of the statement to be executed. How many possible calls are there - I don't know if there's a single point that all execute calls have to go through.

On top of that, the specification needs one point clarified - do the clients also call PL/SQL blocks, and should the log contain only the PL/SQL call, or calls to execute the SQL inside the PL/SQL. Similarly, I suppose, if you have SQL inside pl/sql functions that are called from SQL - which you really shouldn't have, ask Bryn - do you want every such call to be logged; and do you want to avoid logging the sys-recursive calls ?

It might be an idea to look at the features of the Teleran product suggested by Tim to find out what options you need to consider.

Jonathan Lewis

From: <> on behalf of Stefan Koehler <> Sent: 12 August 2017 09:09:51
To: oracle-l;
Subject: Re: Trap SQL statements in network traffic instead of database

Hello Sandy,
no need to purchase any fancy product - AFAIK you are running Oracle on Linux and so you can do all of that with Linux.

Getting the SQL statements (and any other metadata) with SystemTap: * *

Just modify the scripts according to your needs (e.g. logging in a particular format) and that's it :)

P.S.: The overhead is also very little as you only probe on specific C functions (and not all).

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website:
Twitter: _at_OracleSK

> Sandra Becker <> hat am 11. August 2017 um 22:43 geschrieben:
> 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.


Received on Mon Aug 14 2017 - 13:25:30 CEST

Original text of this message