View Oracle Sessions Information in Database and on Linux Server

=========================
Check Current Situation
=========================

SELECT name, value 
  FROM V$PARAMETER 
WHERE name like '%session%' OR name like '%process%'

NAME                           VALUE
------------------------------ ---------------------
processes                      1000
sessions                       1528
session_cached_cursors         100
job_queue_processes            1000
session_max_open_files         10

=========================
See Resource Limit
=========================

COL RESOURCE_NAME for A20

COL LIMIT_VALUE for A20

SELECT resource_name, current_utilization, max_utilization, limit_value

  FROM V$RESOURCE_LIMIT

 WHERE resource_name IN ('processes','sessions');

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE

-------------------- ------------------- --------------- --------------------

processes                            891             944       2000

sessions                             865             986       3072

=========================
See Long Running Sessions
=========================
SELECT * 
  FROM V$SESSION_LONGOPS


=========================
See Sessions Historical Data
=========================

To See historical data on sessions:
Query DBA_HIST_ACTIVE_SESS_HISTORY or V$ACTIVE_SESSION_HISTORY

========================= 

Get list of all sessions 
=========================
SELECT *
 FROM V$ACTIVE_SESSION_HISTORY
 WHERE sample_time BETWEEN TO_DATE('20151119 09:00:00','YYYYMMDD HH24:MI:SS') 
   AND TO_DATE('20151119 10:00:00','YYYYMMDD HH24:MI:SS')
   AND session_type <> 'BACKGROUND' 


========================= 

Get sql text of the sessions
=========================

SELECT * FROM DBA_HIST_SQLTEXT HIST_SQLTEXT 

 WHERE sql_id IN 

(

SELECT sql_id

  FROM V$ACTIVE_SESSION_HISTORY

 WHERE sample_time BETWEEN TO_DATE('20151119 09:00:00','YYYYMMDD HH24:MI:SS') 

   AND TO_DATE('20151119 10:00:00','YYYYMMDD HH24:MI:SS')

   AND session_type <> 'BACKGROUND'   

   AND sql_id is not NULL

)

=========================

Find top SQL statements from last hour
=========================
SET LONG 2000;
SELECT ALL_USERS.username AS user_name,
       V$SQL.module,
       V$SQL.sql_text,
       V$SQL.sql_fulltext,
       NVL(TOP_SQLS.sql_id,'NULL') AS sql_id,
       ROUND(pct_load,1) AS pct_load
FROM(
    SELECT user_id,
           sql_id, 
           count(*),
           count(*)*100/SUM(count(*)) OVER() AS PCT_LOAD
     FROM V$ACTIVE_SESSION_HISTORY     
    WHERE sample_time > sysdate - 1/24
    GROUP BY user_id, sql_id
    )TOP_SQLS,
     V$SQL,
     ALL_USERS
WHERE V$SQL.sql_id(+) = TOP_SQLS.sql_id
  AND ALL_USERS.user_id(+) = TOP_SQLS.user_id
  AND pct_load > 1
  AND username <> 'SYS'
ORDER BY pct_load DESC;

=========================

See Summary of sessions per schema
=========================

COL SCHEMANAME for A30

SELECT schemaname, 
       COUNT(*) sessions  
 FROM V$SESSION 
WHERE schemaname <> 'SYS' 
GROUP BY schemaname 
ORDER BY COUNT(*) DESC;

SCHEMANAME                       SESSIONS

------------------------------ ----------

VRS_GROUP_SHARE                       733

OGG                                    24

COL SCHEMANAME for A30

col MACHINE for A20

SELECT schemaname, program, machine, COUNT(*) sessions

  FROM V$SESSION

 WHERE schemaname <> 'SYS'

GROUP BY schemaname, program, machine

ORDER BY COUNT(*) DESC;

SCHEMANAME      PROGRAM                MACHINE          SESSIONS

--------------- ---------------------- ---------------- ----------

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7WN00002    44

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7EM00002    44

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7WN00001    44

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7IN00006    38

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7IN00005    38

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7IN00002    38

VRS_GROUP_SHARE      JDBC Thin Client  ITMIL7IN00004    38

=========================

Relate sessions to processes

=========================

SELECT 'lsof | grep '||port as lsof_grep, 

        machine, 

        osuser, 

        PROCESSES.spid as server_pid,  

        port, 

        TO_CHAR(logon_time,'YYYYMMDD hh24:mi:ss') AS logon_time, 

        ROUND((SYSDATE-logon_time)*60*60) AS logon_time_hours

   FROM V$SESSION SESSIONS,
        V$PROCESS PROCESSES 
  WHERE schemaname = 'MY_USER'
    AND SESSIONS.paddr = PROCESSES.addr

  ORDER BY machine, PROCESSES.spid;

=========================

See Process details on Linux server 

=========================

ps -p  `lsof | grep 38867 | awk '{print $2}'` -o args