Every support team may come across once in their career to know about how many connections/sessions are in use. I have compiled the list queries which are specific a database.
Microsoft SQL
SELECT spid, hostname, program_name, loginame, waittime, memusage, status, blocked, stmt_start, stmt_end FROM master.sys.sysprocesses;
In this sysprocesses is a system table
The following is the typical query to gather information about connection & session
SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.nt_domain, s.nt_user_name, s.original_login_name, c.connect_time, s.login_time FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE s.login_name= @@LoginName;
The following are the system views/function on top of sysprocesses system table
- sys.dm_exec_connections
- sys.dm_exec_sessions
- sys.dm_exec_requests
Oracle
There are two main database objects they are V$PROCESS & V$SESSION so below is the typical query to gather information about connection & session
select a.spid, b.sid, b.serial#, b.machine, b.username, b.osuser, b.program from v$session b, v$process a where b.paddr = a.addr and type='USER';