topic Re: SQL_TRACE in Operating System - HP-UX

SQL_TRACE

Denys van Kempen — Tue, 06 Apr 2004 04:08:45 GMT

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.

Re: SQL_TRACE

Jean-Luc Oudart — Tue, 06 Apr 2004 04:21:31 GMT

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

Re: SQL_TRACE

Sanjay Kumar Suri — Tue, 06 Apr 2004 04:25:44 GMT

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

Re: SQL_TRACE

Denys van Kempen — Tue, 06 Apr 2004 04:26:34 GMT

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

Re: SQL_TRACE

T G Manikandan — Tue, 06 Apr 2004 04:30:09 GMT

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

Re: SQL_TRACE

Jean-Luc Oudart — Tue, 06 Apr 2004 04:39:09 GMT

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

Re: SQL_TRACE

Denys van Kempen — Tue, 06 Apr 2004 04:42:54 GMT

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.

Re: SQL_TRACE

T G Manikandan — Tue, 06 Apr 2004 04:51:06 GMT

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

Denys van Kempen — Tue, 06 Apr 2004 04:58:54 GMT

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

Unfortunately no cigar either.

Re: SQL_TRACE

Printaporn_1 — Tue, 06 Apr 2004 06:12:12 GMT

just another idea ,
I perfer autotrace feature in sqlplus.

SQL> set autotrace on

Re: SQL_TRACE

Brian Crabtree — Tue, 06 Apr 2004 16:59:12 GMT

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

Re: SQL_TRACE

Yogeeraj_1 — Tue, 06 Apr 2004 23:19:14 GMT

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

Re: SQL_TRACE

Denys van Kempen — Wed, 07 Apr 2004 03:56:32 GMT

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.