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.