cancel
Showing results for 
Search instead for 
Did you mean: 

sql trace

william_39
Occasional Advisor

sql trace

Hi guys,

I have oracle server on HP box, and one of my dba has turned on sql trace on one database, now when he is runing a application (its inhouse application ) he could not see the sql trace file, what could be the reason ?

NOTE : application is written in C, using OCI

Thanks in advance,
william
6 REPLIES
Steven E. Protter
Exalted Contributor

Re: sql trace

Few possibilities:

1) The app is not reading/writing any oracle data.
2) Trace file location defined in control files/init.ora is not where you expect it to be.
3) Trace for the db is turned on in init.ora. Did you bouce the database after making the change?

Note that what the appliction is written in is irrelavent. If you are doing transactions and trace is properly turned on, you will get trace files.

sEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
william_39
Occasional Advisor

Re: sql trace

Hi SEP,
If the application is using only OCI functions to interact with Oracle database, will generate sql trace for it ?

Thanks in adv,
william
Stan_17
Valued Contributor

Re: sql trace

William,

Quick test to check the settings of sql_trace and user_dump_dest parameters.

logon to the database via sqlplus. the user should have access to v$parameter. At the prompt, type

sql> show parameter sql_trace

This should return 'TRUE' if sql_trace turned on otherwise 'FALSE'

also, check the location of the user_dump_dest. thats the location where oracle dumps sql trace files. At the prompt, type

sql> show parameter user_dump_dest

let us know what you find, then we should be able to help you more.

Stan
william_39
Occasional Advisor

Re: sql trace

Hi,
Sql_trace is TRUE and path is also set correct, infact if i run any sql from sql prompt, it is able to generate sql trace, but when i run my application it doesnt generates any trace, even though it is updating database.
So what my concern is, if application is written with the help of OCI function to interact with database, will oracle generate sql trace for OCI functions also ?

thanks
william
Yogeeraj_1
Honored Contributor

Re: sql trace

hi william,

If you environment has been properly configured, as mentioned above, you can determine the name of your trace file by running the following query:

select c.value || '/' || instance || '_ora_' ||
ltrim(to_char(a.spid,'fm99999')) || '.trc'
from v$process a, v$session b, v$parameter c, v$thread c
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Graham Cameron_1
Honored Contributor

Re: sql trace

William
I have never used OCI, only the Oracle precompilers (ie Pro*C), so I don't have direct experience.
However, I found the following on metalink, which may mean something to you or to your application developer....

<metalink>

NO TRACE FILE GENERATED WHEN ALTER SESSION SET SQL_TRACE=TRUE ISSUED IN OCI


You are doing an oparse on the following statement: "alter session set sql_trace=true".
However, when you run the program, you do not get an error, nor do you get the trace file.
Why is this?


Solution Description:
=====================
The reason you get this error is that you do not have an OEXEC after the OPARSE call.
In the Programmer's Guide to the Oracle Call Interfaces, the description for the OPARSE
function says that you do not need an OXEC when executing DDL statements.
ALTER SESSION is NOT a DDL statement. It is a SESSION CONTROL COMMAND.
The SESSION CONTROL COMMANDs do not implicitly COMMIT the current transaction.
.



Hope this helps
-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.