sql trace - very urgent and desperate.

[tamien]

I'm using Oracle 8i. I need to get a sql log file in order to debug a problem, but I don't know how to set up the sql trace in Oracle. I know how to do it in MS SQL. I'm sure someone out there knows how. Please help.
Thanks in advance,
Tamie

[uthaiwan]

you can use the command "alter session set sql_trace = true;" on SQL prompt after that you run your SQL statement on the current session.

[tamien]

Thanks for your help! But I'm not very clear on your answer. Would you please elaborate more? I'm new to Oracle db.

[tansdot]

I dont know what kind of problem you are talking about. Is it a development issue like Pl/sql you need to debug or it is a database tracing issue.
For the Latter you can issue a
Alter session set Sql_trace=true. THis sets tracing on for your session only. It generates atrace file in the location specified by the parameter "user_dump_dest". If there are a lot of trace files in this directory you need to find yours by issuing the following:
select s.username, p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.username = 'USERNAME';
This returns the spid .
The format of the trace file is :SID_ora_SPID.trc

Now you can go through this file and find the parse ,fetch,exec etc info.

Tansdot

[tamien]

Ok. Here is what I try to do.
This is on NT. I'm debugging a problem that I need to look at all the sql statements / db roundtrip that an application makes to Oracle db. Unfortunately, this application doesn't have sql trace option. Thus, I need to generage a sql log file via Oracle application. I read an online doc. which says that I need to update all parameters such as "user_dump_dest" and set "alter session set sql trace = ture" in init.ora. Yet, I can't find init.ora anywhere. What are other things that I need to do besides what I described above.
Thanks million,
Tamie

[tansdot]

First log in to the database as a database administrator.
Init.ora or initSID.ora is usually located in Oracle_home/admin/SID/pfile if the database is in OFA compliance. Otherwise simply find the file init*.

Issue the following to see the init.ora parameters from your database:
select value from v$parameter where name='user_dump_dest' ;(this should be lower case)
Like this you can view the values of all the parameters listed in the init.ora.
Now this particular stmt will show u the location of the user generated trace file. Go to that location & follow my procedure that I sent you in my previous mail to locate the trace file.
If you are trying to debug the sql stmts then probably you need to generate log files in your app code like:
spool db.log
select * from emp;
spool off

Like this you can trace you sql statements. Tracing the db connections your app makes needs to be done from sql net tracing.

Tansdot

[dharma]

you will find initSID.ora where SID can be found from
or SELECT VALUE FROM V$PARAMETER WHERE name='instance_name'.
-dharma

[tamien]

ok. I did the following. I added 2 lines

Alter session set Sql_trace=true
timed_statistics = true

to orant\database\initorcl.ara. I didn't add the dump_dest since it's already in there. It's set as "user_dump_dest=%RDBMS80%\trace". Thus, I assume the trace file is int orant\database\trace. But I didn't see anything after running the application which I'm debugging on.

I don't want to add the codes which you suggested because I'm the only one use the db.

What did I do wrong?

Thank you so much for your time,
Tamie

[kmesser]

By default, user_dump_dest is in:

ORACLE_HOME\admin\sid\udump

if you can't find it, just search for udump using the file find utility in NT.

[tamien]

Thanks for the reply. I did the search on udump file on entire machine, but the file wasn't found. Would you please look at my previous posts to see if I missed any step? I really need this dones asap.

Thanks in advance,
Tamie