enable oracle tracing for a user

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

enable oracle tracing for a user

Post by wgkwvl » Thu 06 Nov 2014 15:52

Hello,

to find some bottle necks , we would like to trace all activity from a certain user.
our database access it self happens on a wcf service , and Entity Framework connects to the database with the same login using Devart for Oracle.
So the database does not know who the user is.
When starting a wcf service call, is check my settings for the current client-user,
and if needed, i try to call a stored proc

PROCEDURE set_session_identifier (p_identifier IN VARCHAR2,
p_tracefile OUT VARCHAR2
)
IS
l_identifier VARCHAR2 (100);
BEGIN
IF p_identifier IS NOT NULL
THEN
l_identifier := REPLACE (p_identifier, 'WGK-WVL\', '');

EXECUTE IMMEDIATE 'ALTER SESSION SET sql_trace = true';

EXECUTE IMMEDIATE
'ALTER SESSION SET tracefile_identifier = "'
|| l_identifier
|| '"';
SELECT --value into p_tracefile
regexp_substr(value, '[^\]*$') into p_tracefile
FROM v$diag_info
WHERE name = 'Default Trace File';
END IF;
END;

before executing any Entity Framework logic.
Since this call happens before any EF logic, it appears to use a different connection, and so my statements do not appear in this trace file.
Is there any way i can make this logic happen at the moment that EF gets its first connection, so i can execute that stored proc in the same session ?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: enable oracle tracing for a user

Post by Shalex » Fri 07 Nov 2014 09:00

We recommend you to implement the StateChange event handler to execute the corresponding command on each connection.Open():

Code: Select all

static void Connection_StateChange(object sender, StateChangeEventArgs e) {
  if (e.CurrentState == ConnectionState.Open) {
    DbConnection connection = (DbConnection)sender;
    DbCommand cmd = connection.CreateCommand();
    // ...
    cmd.ExecuteNonQuery();
  }
}
A complete sample of using StateChange event handler is available in our Code-First blog-article: http://blog.devart.com/entity-framework ... qlite.html. The peculiarities of using EF6: http://blog.devart.com/entity-framework ... force.html.

Also be aware that there is the ClientId connection string parameter (which sets the CLIENT_IDENTIFIER attribute for the session) in dotConnect for Oracle.

Does this help?

wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Re: enable oracle tracing for a user

Post by wgkwvl » Fri 07 Nov 2014 14:00

setting a client_id on the connection makes stuff happen, that i dont like ( open connection gets closed before the business statement is done,

the connection state event stuff helped, thx,
it works now

Post Reply