Oracle SQL query to determine session information

The v$session is one of the most useful Oracle data dictionary tables. This table provides information such as

• The user associated with each session
• The status of a session (active or inactive)
• The program associated with the session
• The machine where the session originated from

This information can be valuable when determining which users or applications are utilizing Oracle. Below is an example of a query that will obtain the number of session associated with each user:

select substr(username,1,10) as username, substr(program,1,30) as program,
status, substr(machine,1,20) as machine, count(*)
from v$session
group by username,status,program, machine
order by status,program,username;

This query will return a result set like the following:

USERNAME	PROGRAM	STATUS	MACHINE	COUNT(*)
-------------	-----------------	--------	----------	--------
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
	oracle@dbsvr	ACTIVE	dbsvr	1
USER1	sqlplusw.exe	ACTIVE	doug-pc	1
USER1		ACTIVE	appsvr	7
USER1	sqlplus.exe	INACTIVE	doug-pc	3
USER2	sqlplus.exe	INACTIVE	doug-pc	1
USER3	sqlplus.exe	INACTIVE	tony-pc	2
USER4	sqlplus.exe	INACTIVE	jeff-pc	3
USER5	sqlplus.exe	INACTIVE	ann-pc	2
USER6	sqlplus.exe	INACTIVE	ann-pc	6
USER1		INACTIVE	appsvr	2

The sessions with the name "oracle" in the program name are created by Oracle and not from users. In this example there are 6 users with sessions. USER1 has a total of 13 sessions (4 from doug-pc and 9 from appsvr). Of the 13 sessions, 8 sessions are active. Note the empty value for the program name for some of the USER1 sessions. These appear to be related to sessions using JBDC.

Oracle's online documentation is an excellent source for learning more about v$session and other data dictionary tables.