Total user count on database:

set lines 750 pages 9999
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session where status in ('ACTIVE','INACTIVE') group by username;

Users Details Session:

For Cluster:

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr
and a.inst_id = b.inst_id and type='USER' order by logon_time;

For Standalone:

set pages 500
set linesize 750
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
select b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from v$session b,v$process a where b.paddr = a.addr
and type='USER' order by b.sid;

(or)

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /+ CHOOSE/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||''||s.process||'' Client, 'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

To Find Session Information Details based on SID or SPID or CLIENTPID :

col program for a15
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||''  Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Timing details, Client PID of associated oracle SID:

undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);

Checking Timing Details of SID and waiting event:

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a, v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);

Checking what is the Last SQL 

undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);

All Active and Inactive connections

col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||''  Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action,
s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.type != 'BACKGROUND';

Active sessions

select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s where p.addr=s.paddr order by substr(s.osuser,1,15);

Session details from Session long ops

select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from v$session_longops where sid=&SID and serial#=&SERIAL;

To list the nodes

set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /+ CHOOSE/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB  ..............: '||SUPPORT_DB
from  apps.fnd_nodes;

Session details through SPID:

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '18924');

To list count of connections from other machines:

select count(1),machine from gv$session where inst_id=3 group by machine;

To get total count of sessions and processes:

select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext through sqladdress:

select sql_address from v$session where sid=8794;

select sql_text from v$sqltext where ADDRESS='A00000089A00FA0' order by PIECE;

To find sqltext for different sql hashvalue:

select hash_value,sql_text from v$sql where hash_value in (193756789,156412346,
456812142,8971248686,2358702945);

To list long running forms user sessions:

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username:

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count() FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count() FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs:

select sid from v$session, v$process where addr=paddr and spid in ('11214','12948','90088');

To find Sql Text given SQLHASH & SQLADDR:

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where  ADDRESS ='&addr' order by piece;

To find Undo Generated For a given session:

select  username,
t.used_ublk ,t.used_urec
from    gv$transaction t,gv$session s
where   t.addr=s.taddr and
s.sid='&sessionid';

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8