SQL_TRACE

[Denys van Kempen]

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.

[Jean-Luc Oudart]

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

[Sanjay Kumar Suri]

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

[Denys van Kempen]

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]

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]

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

[Denys van Kempen]

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]

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

[Denys van Kempen]

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

Unfortunately no cigar either.

[Printaporn_1]

just another idea ,
I perfer autotrace feature in sqlplus.

SQL> set autotrace on