v$session

Refer it contains all the sql related to v$session https://oracledbwr.com/check-oracle-active-and-inactive-sessions/ 

1)

Total Count of sessions

select count(s.status) TOTAL_SESSIONS

from gv$session s;

Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status=’INACTIVE’;

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) “INACTIVE SESSIONS > 1HOUR ”

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status=’INACTIVE’;

COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status=’ACTIVE’;

col username for a25

set line 1234

select username, status, count(*) from v$session group by username,status order by username,status;

select username, status, machine, count(*) from v$session group by username,status, machine order by 1,2,3;

select sid,serial#,status,sql_id, status, machine from v$session where username='HR';

select sql_text from v$sql where sql_id='.........'

select sql_text from v$sqlarea where sql_id='.........'

select sql_text from v$sqltext where sql_id='.........'

select * from v$session_longops where time_remaining!=0;