Query to know Process, Connection & Session of a DB

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';