sql trace

[william_39]

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

[Steven E. Protter]

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

[william_39]

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]

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]

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]

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

[Graham Cameron_1]

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