Session Related SQLs

 Session Related SQLs

--User wise Session Count

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180

col username for a40

compute sum of cnt on report

break on report

select username, count(1) cnt from v$session group by username order by 2 desc ;

--User & machine wise Session Count

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col username for a40

compute sum of cnt on report

break on report

select machine,username, count(1) cnt from v$session group by username, machine order by 1 desc ;

--User, machine wise Last Logon Details

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
select username,machine, max(LOGON_TIME) "LAST LOGON"  from v$session
group by username,machine 
order by 1;

--User, machine wise Last Logon & Session Count Details 

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select sysdate from dual;

set pages 30

set lines 180

col username for a40

col osuser for a20

select username,machine,osuser, max(LOGON_TIME) "LAST LOGON", count(1)  from v$session

where username not in ('SYS','RDSADMIN')

group by username,machine,osuser 

order by 1,2,4;

-- historical details of connections (processes)

SELECT RL.RESOURCE_NAME,

RL.SNAP_ID,

S.BEGIN_INTERVAL_TIME,

S.END_INTERVAL_TIME,

RL.CURRENT_UTILIZATION,

RL.MAX_UTILIZATION

FROM DBA_HIST_RESOURCE_LIMIT RL,

DBA_HIST_SNAPSHOT S

WHERE S.SNAP_ID = RL.SNAP_ID

AND RL.RESOURCE_NAME = 'processes'

and S.BEGIN_INTERVAL_TIME > sysdate-1

order by 2 desc;

--(sysdate -1) for last 24 hours

-- sql related details of a user

COL OSUSER FOR a20;

COL USERNAME FOR a40;

COL MACHINE FOR a20;

SELECT

    a.sid,

    a.serial#,

    a.osuser,

    a.username,

    a.machine,

    a.sql_id,

    c.sql_text

FROM

    v$session a,

    v$process b,

    v$sql     c

WHERE

        a.paddr = b.addr

      and a.username ='&user'

    AND a.sql_id = c.sql_id (+);