CODEWITHSUNDEEP
oracle11g
Gloria Santin
Gloria Santin

Reputation: 2136

Find connecting application to oracle database using session ID

I am trying to determine the application that is using a specific session ID in an Oracle 11g database. I can find the service name of the session id using this query

select SID, SERVICE_NAME
from v$session
where SID = <sessionID here>;

Unfortunately, several applications use this service name to connect to the database. Is there another table/query that I can use to determine which application is using this session ID?

Upvotes: 1

Views: 1664

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

There are other columns in V$SESSION that might be useful, e.g.:

select username, osuser, logon_time, status, program, module, client_identifier, client_info
from gv$session
where sid = <sessionID here>;

However it is up to your application to set or override the last four values.

For example, a session started from SQL*Plus shows program as something like sqlplus@client_host (TNS V1-V3) and module as SQL*Plus. SQL Developer shows both values as that name. A JDBC connection will have something like 'JDBC Thin Client' for both by default, but the application can override those and/or set client_identifier and client_info if it chooses to. As can a Pro*C application, which defaults to both program and module looking something like exe_name@client_host (TNS V1-V3).

As SQL Developer is using JDBC, that is overriding both program and module with it's own value; and SQL*Plus is overriding the default module value.

So if the application using the session ID you're interested in has configured any of those values you should get a pretty good idea of what it is. And if it hasn't then you may still get clues about the kind of application (e.g. JDBC). The osuser might be useful too.

See SYS_CONTEXT and the DBMS_APPLICATION_INFO and DBMS_SESSION packages for more about how an application can set those values.

Upvotes: 2

Related Questions