Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL_TRACE

SOLVED
Go to solution

SQL_TRACE

Hi,

I'm running Oracle9i Enterprise Edition Release 9.2.0.4.0 64-bit on a HP-UX rx2600 ia 64-bit B11.23 (11i version 2) box. For some reason SQL_TRACE can not be activated.
I tried:
* SQL_TRACE=TRUE in the init file
* ALTER SESSION
* SET EVENTS
* LOGON TRIGGER that does an alter session or a set events.
I check that the software owner can write to the directory specified by the parameter user_dump_dest and that the file system is not full.

Anybody out there with a similar config where SQL Trace works?

Thanks.
12 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: SQL_TRACE

what is the output for :
select name, value from v$parameter where name like '%trace%';
select name, value from v$parameter where name like '%dump%';

Jean-Luc
fiat lux
Sanjay Kumar Suri
Honored Contributor

Re: SQL_TRACE

Add to previous post:

SQL_TRACE is an initialization parameter. However, when you change its value with an ALTER SESSION statement, the results are not reflected in the V$PARAMETER view. Therefore, in this context it is considered a session parameter only.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.

Re: SQL_TRACE

Thanks for your reply:

Here is the output.

SQL> /

NAME VALUE
---------------------------------------- ----------------------------------------
shadow_core_dump partial
background_core_dump partial
background_dump_dest /u01/app/oracle/admin/THALES/bdump
user_dump_dest /u01/app/oracle/admin/THALES/udump
max_dump_file_size UNLIMITED
core_dump_dest /u01/app/oracle/admin/THALES/cdump

6 rows selected.

NAME VALUE
---------------------------------------- ----------------------------------------
tracefile_identifier
trace_enabled TRUE
log_archive_trace 0
oracle_trace_enable FALSE
oracle_trace_facility_path ?/otrace/admin/fdf
oracle_trace_collection_path ?/otrace/admin/cdf
oracle_trace_facility_name oracled
oracle_trace_collection_name
oracle_trace_collection_size 5242880
sql_trace TRUE
T G Manikandan
Honored Contributor

Re: SQL_TRACE

it is not only sql_trace you should also set timed_statistics=true in your init.ora file

sql_trace=true
timed_statistics=true
user_dump_dest=

restart database.



Else

alter system set sql_trace=true
alter system set timed_statistics=true

Revert with the results
Jean-Luc Oudart
Honored Contributor

Re: SQL_TRACE

The timed_statistics won't affect the SQL trace itself but will give you the time information on the trace. This is true that a trace without time information won't be as useful.

try
alter system set timed_statistics=true;
execute sys.dbms_system.set_sql_trace_in_session( , ,true);

Does this work ?

Regards,
Jean-Luc
fiat lux

Re: SQL_TRACE

Timed statistics happens to be true. But this has nothing to do whether SQL Trace works or not. I know how to activate SQL trace and all. I just wondered if anybody has encountered a bug with this specific Oracle release on this specific HP platform.

Thanks anyway.
T G Manikandan
Honored Contributor

Re: SQL_TRACE

I would also check the alert*.log and trc files under bdump,cdump directories for the exact problem.

Do you have permissions on the dir where the trace files are pointed out

Re: SQL_TRACE

I tried dbms_system.set_sql_trace_in_session(10,91,true);

Unfortunately no cigar either.
Printaporn_1
Esteemed Contributor

Re: SQL_TRACE

just another idea ,
I perfer autotrace feature in sqlplus.

SQL> set autotrace on
enjoy any little thing in my life
Brian Crabtree
Honored Contributor
Solution

Re: SQL_TRACE

Denys,

Check in the $ORACLE_HOME/rdbms/log and $ORACLE_HOME/rdbms/trace directory. This is the default directory that is used when oracle can't write to the original directory. Occasionally this does happen.

Thanks,

brian
Yogeeraj_1
Honored Contributor

Re: SQL_TRACE

hi,

have a look at your alert.log. There could be error messages logged about this issue. If you think that you are really facing a bug then you must surely create a TAR on metalink.

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

Re: SQL_TRACE

Indeed, traces were dumped to $ORACLE_HOME/rdbms/log eventhough the UDUMP directory was valid and writable and the alert.log was correctly located at the BDUMP directory indicated. Strange. Whatever, i've got my traces. Thanks everybody.