dbms_monitor does not write trace file

[Former Member]

Hi Forum,

I try to activate a trace on a java system...

When I execute the following as SYS, no trace gets written to user_dump_dest...

Any ideas what I am doing wrong:

"exec dbms_monitor.client_id_trace_enable (client_id=>'SAPSR3DB');"

Ora Version is: 10.2.0.2

Thank you,

Chris

[fidel_vales · Accepted solution]

Hi,

I'm not familiar with DBMS_MONITOR, but your statement:

> "exec dbms_monitor.client_id_trace_enable (client_id=>'SAPSR3DB');"

raise RED alerts.

So I take a look at the [documentation|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_monitor.htm#sthref4019]:

CLIENT_ID_TRACE_ENABLE Procedure
This procedure will enable the trace for a given client identifier globally for the database.
Syntax
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
   client_id    IN  VARCHAR2,
   waits        IN  BOOLEAN DEFAULT TRUE,
   binds        IN  BOOLEAN DEFAULT FALSE);
Parameters
Table 60-5 CLIENT_ID_TRACE_ENABLE Procedure Parameters
Parameter 	Description
client_id  Database Session Identifier for which SQL tracing is enabled.
waits If TRUE, wait information is present in the trace.
binds If TRUE, bind information is present in the trace.

You have NOT provided any client_id, you are providing a "USERNAME".

Oracle is not able to find any client_id that matches your "USERNAME".

Conclusion, works as desing.

Please, read properly the documentation for the package [DBMS_MONITOR|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_monitor.htm]

[Former Member]

Hi Chris,

you have to specify a client id (it's different from the USER name) AND it's of use if you want to collect several session's SQL for the same client_id.

Later you can use TRCSESS to combine the output of the sessions:

-- session 1

sql> exec dbms_session.set_identifier( 'SAPSR3DB');

sql> show user
USER is "ORADBA"

sql> exec dbms_monitor.client_id_trace_enable( 'SAPSR3DB' );


-- session 2

sql> exec dbms_session.set_identifier( 'SAPSR3DB');

sql> show user
USER is "ORADBA"

sql> exec dbms_monitor.client_id_trace_enable( 'SAPSR3DB' );

-- go to the dump directory and find your trace files and combine the outputs with TRCSESS:

trcsess output="Consolidated.trc" clientid="SAPSR3DB" 

for further reading check:

http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html

bye

yk

[Former Member]

Thanks to all of you... I was confused between the user name and the client id...

With a trigger I am setting the client id when my SAP connect to Oracle and afterwards I use dbms_monitor.client_id_trace_enable(client_id=>varchar2 c) to enable the trace.

create or replace trigger logon_trigger_t 
after logon 
on database
declare
	user_id varchar(64);
begin
	select ora_login_user into user_id from dual;
	dbms_session.set_identifier(user_id);
end;
/

Edited by: Chris Sell on Jul 13, 2009 6:03 PM

[fidel_vales]

hi,

do you really want to trace absolutely everything that SAP does?

is your filesystem big enough?

also note that in new kernels SAP will set the identifier ID (I think it is from 7.10, but not sure)

[Former Member]

Hi,

It appears that the syntax for enabling trace is wrong.

The syntax is as follows:

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(

client_id IN VARCHAR2,

waits IN BOOLEAN DEFAULT TRUE,

binds IN BOOLEAN DEFAULT FALSE);

where,

client_id

Database Session Identifier for which SQL tracing is enabled.

waits

If TRUE, wait information is present in the trace.

binds

If TRUE, bind information is present in the trace.

eg, EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('<username>', TRUE, FALSE);

regards,

Subhajit.

[fidel_vales]

Hi,

<Mode ironic on>

It looks like reading is a skill missing around

<Mode ironic off>

I mentioned in my previous post to read the documentation and pasted the relevant part, just in case someone was to lazy to read it.

You add no value to my post (as you only copied the documentation I already posted)

Not only that, but you made the same mistake as the OP:

> eg, EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('<username>', TRUE, FALSE);

that is what the OP was doing:

> "exec dbms_monitor.client_id_trace_enable (client_id=>'SAPSR3DB');"

Note that, in the part that you copied it is indicated the default values of "waits" (TRUE) and "binds" (FALSE)

The mistake both of you made is think that the "client_id" is the "USERNAME".

As per the documentation (copied by you and me):

> client_id Database Session Identifier for which SQL tracing is enabled.

Username is not a Session Identifier.

You can find the client_id using the following query:

SELECT sid, serial#, client_identifier FROM V$SESSION

Thanks for reading me and the V$SESSION documentation

[udaykishoreka]

CLIENT_ID_TRACE_ENABLE

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE (client_id, waits, binds);

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(' ' )