User Session Scripts

alter user SCOTT account lock;

alter user SCOTT account unlock;

select username,ACCOUNT_STATUS from dba_users where username like '%SCOTT%';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from gv$session where username='SCOTT' and status ='INACTIVE';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from gv$session where username='SCOTT' and status ='ACTIVE';

select 'alter system kill session '''||a.sid||',' ||a.serial#||''' immediate ;'
from v$session a
where event='library cache: mutex X'
and username in( 'PR_PRE','PRD_SE')
and status='ACTIVE';

select count(*) ,username,status from v$session where username='SCOTT' and status='ACTIVE' group by username,status;

select sid,
      serial#,
      username,
      osuser,module,
      to_char(sysdate - last_call_et / 24 / 60 / 60, 'hh24:mi:ss') started,
      status,
      to_char(logon_time, 'DD/MM/YYYY HH24:MI') login,
      floor((sysdate - logon_time) * 24) || ' HOURS ' ||
      mod(floor((sysdate - logon_time) * 24 * 60), 60) || ' MINUTES ' ||
      mod(floor((sysdate - logon_time) * 24 * 60 * 60), 60) || ' SECS ' logon_since,
      sysdate,
      trunc(last_call_et / 60) || ' mins, ' || mod(last_call_et, 60) ||
      ' secs' dur,
      CLIENT_INFO,
      ACTION,
       (select sql_text from v$sql where address = sql_address and rownum=1) sql_text
  from v$session
 where username = 'W6ADMINSODB' and module ='w3wp.exe'
  and status = 'ACTIVE'
 and logon_time < (sysdate - (1 / 24) * (45 / 60));

set pages 200 lines 200
col USERNAME for a10
col TERMINAL for a10
col OSUSER for a10
col MACHINE for a20
spool /opt/backup/oracle/USER_LOGIN.log
select USERNAME,OSUSER,LOGON_TIME,SID,SERIAL# ,MACHINE,TERMINAL,STATUS,PROGRAM from v$session where USERNAME='SCOTT' and upper(PROGRAM) like '%SQL D%';
spool off;

set linesize 95
set head on
set feedback on
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a8 trunc
col osuser form a7 trunc
col machine form a20 trunc head "Client|Machine"
col program form a15 trunc head "Client|Program"
col login form a11
col "last call" form 9999999 trunc head "Last Call|In Secs"
col status form a6 trunc
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,
substr(program||module,1,15) program,substr(machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,
last_call_et "last call",status
from gv$session where status='ACTIVE'
order by 1
/

set pages 100
set lines 200
column username format a30
column limit format a10
column count() format 999,999 column inst_id format 999 select s.inst_id,s.username,count(),p.limit,(round(count()/p.limit100)) as percentFull from gv$session s,dba_users u,dba_profiles p where s.username=u.username and u.profile=p.profile and p.resource_name ='SESSIONS_PER_USER' and p.limit not in ( 'UNLIMITED', 'DEFAULT') group by s.inst_id, s.username, p.limit order by percentFull desc, username, inst_id;

select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

select count(s.status) TOTAL_SESSIONS from gv$session s;

select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';

select
 resource_name,
 current_utilization,
 max_utilization,
 limit_value
from
 v$resource_limit
where
 resource_name in ( 'sessions', 'processes');

select 'ALTER SYSTEM DISCONNECT SESSION '''||sid||','||serial#||'''immediate;' from V$session where username='rman'
and status = 'INACTIVE';

select count(*),username,status,machine,osuser,INST_ID from gv$session
where program not like 'ORACLE%'
--and username='USR2'
--and username='USR1'
group by username,status,machine,osuser,INST_ID
order by username
/