Re: trapping SQL*NET traffic

From: Chris Kasten <kasten_at_brookings.net>
Date: 1996/04/09
Message-ID: <316a55d4.85042677_at_news.brookings.net>#1/1

snmauf_at_ccmail.monsanto.com wrote:

>
> I am trying to see the SQL statements a vendor is sending up and down to/from
> an Oracle database running on a UNIX box from an NT client running SQL*NET V2.
> Rest assured I don't want to do this to acquire some proprietary knowledge,
> just debug a data problem. I'd love to see,
>
> INSERT into table values (1,2,3,4)
>
> in clear text someplace. The SQLNET.ORA debug parameter seems to dump out more
> network handshaking stuff, and I need the raw SQL statements.
>
> What you get from the ODBC flags would be wonderful, were I using ODBC...
>
> If kind souls could email me at SNMAUF_at_CCMAIL.MONSANTO.COM I promise to post
> the final answer to the newsgroup..
>
> Thanks ! Simon
> PS I'm not keen enough to put an Ethernet sniffer on the line!
>

Instead of mucking about with the network, why not go to the database and let it tell you what's going on? Many third party products will show you currently/recently executed SQL statements. Or you could run a script like the following (was posted to this group within the last month):

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- rem Contributed by:

rem Brian M. Biggs                             mailto:bbiggs_at_cincom.com

SET PAGESIZE 60
SET FEEDBACK ON
SET TERMOUT OFF

COLUMN sid         HEADING "SID"         FORMAT 990
COLUMN user_name   HEADING "USERNAME"    FORMAT A14 TRUNCATED
COLUMN sql_text    HEADING "SQL TEXT"    FORMAT A40
COLUMN executions  HEADING "EXECS"       FORMAT 999999
COLUMN sorts       HEADING "SORTS"       FORMAT 999
COLUMN buffer_gets HEADING "BUFFER|GETS" FORMAT 999
COLUMN disk_reads  HEADING "DISK|READS"  FORMAT 999

BREAK ON user_name SKIP PAGE

COLUMN name NEW_VALUE db_name NOPRINT
SELECT name FROM v$database
/

SET TERMOUT ON PROMPT
PROMPT Open cursor information for database &&db_name:

SELECT a.user_name || '(' || b.sid || ')' AS user_name,
       c.sql_text, c.sorts, c.executions,
       c.disk_reads, c.buffer_gets

FROM v$open_cursor a, v$session b, v$sqlarea c WHERE a.saddr = b.saddr AND a.address = c.address ORDER BY a.user_name
/

CLEAR BREAKS
CLEAR COLUMNS
SET PAGESIZE 14 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Chris Kasten
Programmer/Analyst

#include <std/disclaimer.h>
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Received on Tue Apr 09 1996 - 00:00:00 CEST