No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. Péran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1485
Act. users last 24h4
Act. users last hour0
Registered user hits last week184
Registered user hits last month815
Go up

Extract SQl from SQLNet trace files
Next thread: oracle saas product
Prev thread: RMAN PITR - Table from PROD to NON PROD environment

Message Score Author Date
Hi, Does someone know how to extract the SQLs f...... Lauri Dec 02, 2019, 12:27
Hello Lauri, you give the answer than you "deny...... Bruno Vroman Dec 02, 2019, 18:37
Hi Bruno, Here is the content of my sqlnet.ora:...... Lauri Dec 03, 2019, 09:57
Hello, oh, this is not a "session trace files" ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Dec 03, 2019, 14:24
Hi, > Any other idea? Sure. Use the correct ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Dec 03, 2019, 14:37
Hi Bruno, You posted something interesting but ...... Lauri Dec 04, 2019, 12:47
Hi Lauri, I see your comment <<<I>I have tried ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Dec 04, 2019, 13:42

Follow up by mail Click here


Subject: Extract SQl from SQLNet trace files
Author: Lauri, Netherlands
Date: Dec 02, 2019, 12:27, 4 days ago
Os info: Linux x86 64bi
Oracle info: 12c and higher
Error info: n/a
Message: Hi,

Does someone know how to extract the SQLs from an SQLNet trace file?
It is to be guessed in the trace file itself, but easily readable.
I have tried with tkprof (seldom use this utility) but I cannot get the code.

Thanks by advance.
Kind Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Extract SQl from SQLNet trace files
Author: Bruno Vroman, Belgium
Date: Dec 02, 2019, 18:37, 4 days ago
Message: Hello Lauri,

you give the answer than you "deny it"?
if you have a session in trace mode, tkprof can show you the statements that have been run (as well as various additional information depending of the trace level)

So what is not OK with tkprof? What do you mean by <but I cannot get the code.>?

You might simply type tkprof<enter> to have some info about the possibilities:
  $ tkprof
The "basic" call is
  $ tkprof mytrace.trc myfile.txt
(most of the time I also use sys=no and often I use a "sort" option)

Best regards

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Extract SQl from SQLNet trace files
Author: Lauri, Netherlands
Date: Dec 03, 2019, 09:57, 4 days ago
Message: Hi Bruno,

Here is the content of my sqlnet.ora:
---
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
#TRACE_LEVEL_SERVER = 16
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = sqlnet_trace_server.trc
TRACE_DIRECTORY_SERVER = /u03/oracle/logs/trace
TRACE_UNIQUE_SERVER = ON
TRACE_TIMESTAMP_SERVER = on
LOG_DIRECTORY_SERVER = /u03/oracle/logs/sqlnet
LOG_FILE_SERVER = sqlnet_server.log
DIAG_ADR_ENABLED = off
ADR_BASE = /u01/oracle/base
---

No, the tkprof does not show up the statements, only statistics. When I run that statement:
tkprof sqlnet_trace_server_3245.trc topaz_3245.txt

Where 3245 is de SPID of the session that run the SQL statements, Here is the content I get:
---
TKPROF: Release 12.1.0.2.0 - Development on Tue Dec 3 09:48:40 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Trace file: sqlnet_trace_server_3245.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: sqlnet_trace_server_3245.trc
Trace file compatibility: 11.1.0.7
Sort options: default

0 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
2912 lines in trace file.
0 elapsed seconds in trace file.
---

Any other idea?

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re-Extract SQl from SQLNet trace files
Author: Bruno Vroman, Belgium
Date: Dec 03, 2019, 14:24, 3 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello,

oh, this is not a "session trace files" in which you can find statements but a sqlnet trace file.
Why do you create the file? To send info to Oracle for a Service Request?

If you want to see statements run by a session, you have to trace the session.

If this can help, you can trace a session "as soon as it starts" via a trigger, have a look at
https://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=3764

(
also one thing to know when you start to trace a session: the trace doesn't go back in time so if for example at time t0 a statement S starts and takes a long long time and at time t1 you set the session in trace mode and later tkprof it, you will not see the details of the statement S as the trace contains only bits of its execution (read this piece, wait, read that piece, …).
It is important that the session is in trace mode before the beginning of the relevant statement so that info about the statement itself is contained in the trace file (hence the trick with the "trigger to trace from logon time")
)

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re-Extract SQl from SQLNet trace files
Author: Ales Kavsek, Slovenia
Date: Dec 03, 2019, 14:37, 3 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

> Any other idea?

Sure. Use the correct tool for the job.

tkprof is a tool used for parsing "SQL Trace" files, not sqlnet trace files.

If you want to catch SQL's use SQL Trace, not sqlnet trace.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Extract SQl from SQLNet trace files
Author: Lauri, Netherlands
Date: Dec 04, 2019, 12:47, 2 days ago
Message: Hi Bruno,

You posted something interesting but unfortunatelly it does not work.
It does not catch simple statements (what I have tested).

I have been somewhat confused.
SQL Net and SQL tracing are indeed two different things.
I was thinking I could catch SQL "info" through SQL Net tracing... but that's not the case.

The only thing I see is the setting SQL trace for a given session (sid, serial#), let generate the trace file, format it with tkprof for furher interpretattion.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re-Extract SQl from SQLNet trace files
Author: Bruno Vroman, Belgium
Date: Dec 04, 2019, 13:42, 2 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Lauri,

I see your comment <<I have tried to catch information from simple SQL statements against Oracle 12c1 and 12c2 but I don't get any trace file. >>

I use such triggers quite regularly against 12.1.0.2 without problem...

Of course you have to put the right filter (most probably not "SCOTT"), and don't use something like this against "SYS".

Please create a small test: create a new user MYTEST in your database (with create session and for example create table (+quota on a tablespace) and select any table, anyway the user should exist only a very short time),
create the trigger " WHEN ( user = 'MYTEST' ) ", connect as MYTEST and perform a few statements then exit.

It would be amazing if no trace is created. In this case, please copy&paste the CREATE TRIGGER statement...
(and at the end of the test: drop user MYTEST and drop the trigger)

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here