Check session status active, inactive, sql query in Oracle

Session information in oracle

You are able to get the info about the session and get details about session. such as How many session is active? , How many session running from specific module?
how many session is inactive? , how much time a session is in inactive state? and how to kill the inactive session?

Session Currently connected to Database:

set pagesize 100
set linesize 132
col logontime format a15
col username format a10
col terminal format a13
col program format a49
col osuser format a13
col status format a8
col sid format 9999
SELECT to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') logontime,
username,terminal,osuser,status,sid,serial#,program
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time DESC;

 
Find the session and process id for particular session:

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where b.paddr = a.addr
and type='USER'
order by spid;

Currently Active session with detailed SQL Queries on Database:

set pagesize 100 linesize 132
col username format a10
col sql_text format a60
SELECT USERname,terminal,SID,a.module,
SERIAL#,SQL_TEXT
FROM V$SESSION a, V$SQL b
WHERE a.SQL_ADDRESS = b.ADDRESS
AND a.STATUS = 'ACTIVE'
AND SQL_TEXT not like '%USERname%'
AND username IS NOT NULL;

 
Currently Active Session on Database with login time

set pagesize 100
set linesize 132
col logontime format a15
col username format a10
col terminal format a13
col program format a49
col osuser format a13
col status format a8
col sid format 9999
SELECT to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') logontime,
username,terminal,osuser,status,sid,serial#,program,audsid
FROM v$session
WHERE username IS NOT NULL and status='ACTIVE'--and module like 'Host%'
ORDER BY logon_time DESC;

 
Currently Inactive Session on Database:

set pagesize 100
set linesize 132
col logontime format a15
col username format a10
col terminal format a13
col program format a49
col osuser format a13
col status format a8
col sid format 9999
SELECT to_char(logon_time,'DD/MM HH24:MI:SS') logontime,
username,terminal,osuser,status,sid,serial#,program,audsid
FROM v$session
WHERE username IS NOT NULL and status='INACTIVE'--and module like 'Host%'
ORDER BY logon_time DESC;

 
Check Inactive Session which is not used from 24 hours or more.

--check inactive session time with minute since last active
select username, UPPER(program), logon_time,
floor(last_call_et / 60) "Minutes since active", status
from v$session
where UPPER(program) LIKE '%W3%'
order by last_call_et;

Column in V$session view:
LAST_CALL_ET : its consider value in seconds, means not run any query from last 24 hours if we value greater than 60*60*24
As Documentation:
Last Call Value:
STATUS is ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.


-- Session is not used in last 24 hours(1 days)
SELECT SID, SERIAL#,MODULE, STATUS FROM V$SESSION S WHERE S.USERNAME IS NOT NULL AND UPPER(S.PROGRAM) IN ( 'W3WP.EXE')
AND S.LAST_CALL_ET >= 60*60*24 AND S.STATUS = 'INACTIVE' ORDER BY SID DESC;

-- Session is not used in last 48 hours(2 days) and so on ..
SELECT SID, SERIAL#,MODULE, STATUS FROM V$SESSION S WHERE S.USERNAME IS NOT NULL AND UPPER(S.PROGRAM) IN ( 'W3WP.EXE')
AND S.LAST_CALL_ET >= 60*60*48 AND S.STATUS = 'INACTIVE' ORDER BY SID DESC;

 
Find count of Active and Inactive Sessions:

select count(*) from v$session where status = 'ACTIVE';
select count(*) from v$session where status = 'INACTIVE';

 
Find the status of application like how much session and old session present in oracle

select to_char(logon_time,'DD/MM HH24:MI:SS') logontime from V$session
where UPPER(program) LIKE '%W3%'
order by logon_time;

 

Check the SQL queries for particular module:

set pagesize 100 linesize 132
col username format a10
col sql_text format a60
SELECT USERname,terminal,SID,a.module,
SERIAL#,SQL_TEXT
FROM V$SESSION a, V$SQL b
WHERE a.SQL_ADDRESS = b.ADDRESS and UPPER(program) LIKE '%W3%'
--AND a.STATUS = 'ACTIVE'
--AND SQL_TEXT not like '%USERname%'
AND username IS NOT NULL;

 

Sessions are present in Oracle database as inactive state
Application is making lot of session and it will be as it’s with database for long time and also cause the below error:
Getting error ORA-00018: maximum number of sessions exceeded
To avoid such error DBA need to prepared as follows:

1. Check the resource limit for session and process:

select * from v$resource_limit;

Find the reason why session is connected, For stopping we can change the profile idle_time from unlimited to value we needed.

1. Check the user belong to which profile

select username, profile from dba_users where username ='SCOTT';

2. Check the profile ideal_timeout value:

select profile, resource_name, limit from dba_profiles where profile='PROFILE1' and resource_name ='IDLE_TIME'
OR
select * from dba_profiles where profile = 'DEFAULT';

 

2nd way to break idle connection is sqlnet.ora file, SQLNET.ora is client specific file.

EXPIRE_TIMEOUT in sqlnet.ora

 
For Manually Kill the session:

select sid,serial#,USERNAME,status from v$session where sid in (3048);

alter system kill session '1066,3292' immediate;

select 'alter system kill session "'|| sid ||','||serial# ||"' immediate;' from v$session
where sid in (1645, 1757,2100,827,1678,1609,1653,1640,1730);

Complete details for Running Session

SELECT s.inst_id,s.event,s.p1,s.p2,
s.status "Status", s.sid, s.serial# "Serial#", p.spid "OS Proc", s.sql_hash_value "Sql Hash",
ROUND(s.last_call_et/60, 2) "Run Min", s.MODULE "Module", s.logon_time "Connect Time",s.username "DB User", s.osuser "OS User", s.machine "Machine", s.TYPE "Type", s.client_info "Client Info", s.server "Server", s.terminal "Terminal",
s.program "Program", p.program "O.S. Program", lockwait "Lock Wait",s.process "Process", p.pid, s.audsid, s.sql_address "Address", s.action, s.blocking_session_status, s.blocking_instance, s.blocking_session, (select sql_fulltext from gv$sql where hash_value=s.sql_hash_value and rownum<2) query
FROM Gv$session s, Gv$process p
WHERE s.paddr = p.addr(+)
— AND s.sid = :s1
— and s.osuser like 'oracle%'
— and s.username= '&schema_name'
and s.status='ACTIVE'
—and s.blocking_session is not null
AND (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') 'SYSTEM')
AND (s.TYPE 'BACKGROUND')

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s