select username,ACCOUNT_STATUS from dba_users where username like '%SCOTT%';
CHECK AND GET SID & SERIAL# ID OF SPECIFIC INACTIVE AND ACTIVE SESSION
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';
CHECK AND KILL ALL SYS USER RUNNING WITH LIBRARY CACHE MUTEX WAIT EVENTS TO ESTABLISH NEW CONNECTION
ERROR: MAX PROCESS EXCEED
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';
CHECK COUNT OF SPECIFIC USER
select count(*) ,username,status from v$session where username='SCOTT' and status='ACTIVE' group by username,status;
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;
ACTIVE SESSION DETAILS M/C,PROGRAM,SID,TIME
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
/
SESSION UTILIZATION DETAILS
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;
INACTIVE SESSION
select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';
TOTAL SESSION
select count(s.status) TOTAL_SESSIONS from gv$session s;
ACTIVE SESSION
select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';
MAX SESSION AND MAX PROCESS & CURRENT UTILIZATION
select
resource_name,
current_utilization,
max_utilization,
limit_value
from
v$resource_limit
where
resource_name in ( 'sessions', 'processes');
DISCONNECT USER CONNECTION MORE THAN 2HRS
select 'ALTER SYSTEM DISCONNECT SESSION '''||sid||','||serial#||'''immediate;' from V$session where username='rman'
and status = 'INACTIVE';
CHECK USER DETAILS
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
/
