{"id":3795,"date":"2025-06-08T02:20:00","date_gmt":"2025-06-08T02:20:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=3795"},"modified":"2025-06-08T16:14:35","modified_gmt":"2025-06-08T16:14:35","slug":"oracle-session-management","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-session-management\/","title":{"rendered":"Oracle Session Management"},"content":{"rendered":"\n

An Oracle session<\/strong> represents a single connection from a user or application to the database. Monitoring and managing sessions is key for performance, troubleshooting, and resource control.<\/p>\n\n\n\n

Check Session Details<\/h2>\n\n\n\n
set line 200 pages 200\ncol USERNAME for a15\ncol STATUS for a15\ncol OSUSER for a15\ncol machine for a15\ncol spid for a10\nalter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';\nSELECT s.sid, s.serial#, s.username, s.osuser,s.STATUS, p.spid,s.sql_id, s.machine, p.terminal,s.LOGON_TIME, s.program\nFROM v$session s, v$process p\nWHERE s.paddr = p.addr and s.sid='&SID';<\/code><\/pre>\n\n\n\n
Terminate All Sessions for a Specific SQL_ID<\/h2>\n\n\n\n
select 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;' from v$session  \nwhere sql_id='&sql_id'; <\/code><\/pre>\n\n\n\n
FOR RAC- kill all sessions of a sql_id<\/h2>\n\n\n\n
select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'  \nfrom gv$session where sql_id='&sql_id'<\/code><\/pre>\n\n\n\n
Kill all session of a user<\/h2>\n\n\n\n
BEGIN \nFOR r IN (select sid,serial# from v$session where username = 'W3BUDDY') \nLOOP \nEXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  \n|| ',' || r.serial# || ''''; \nEND LOOP; \nEND; \n\/<\/code><\/pre>\n\n\n\n
Inactive session check<\/h2>\n\n\n\n
set lines 300 pages 200\ncol machine for a30\ncol status for a20\nselect sid,serial#,username,program,machine,status,sql_id,logon_time from v$session where status='INACTIVE' and type <> 'BACKGROUND' and sql_id is NULL;<\/code><\/pre>\n\n\n\n
Inactive session check for a user<\/h2>\n\n\n\n
set lines 300 pages 200\ncol machine for a30\ncol status for a20\nselect sid,serial#,username,program,machine,status,sql_id,logon_time from v$session where status='INACTIVE' and username='W3BUDDY' and type <> 'BACKGROUND';<\/code><\/pre>\n\n\n\n
Inactive session check for a user where sql_id is NULL<\/h2>\n\n\n\n
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE' and username='W3BUDDY' and type <> 'BACKGROUND' and sql_id is NULL;<\/code><\/pre>\n\n\n\n
Generate dynamic commands to kill all the inactive session for a user<\/h2>\n\n\n\n
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE' and username='W3BUDDY' and type <> 'BACKGROUND'; <\/code><\/pre>\n\n\n\n
Check Blocking Sessions (My Favourite)<\/mark><\/h2>\n\n\n\n
set lines 200\ncol sess format a15\nSELECT DECODE(request,0,'Holder: ','  Waiter: ')||sid sess,id1,id2, lmode,inst_id, request, type,ctime\nFROM GV$LOCK    WHERE (id1, id2, type)\nIN\n(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)     ORDER BY id1,       request\n\/<\/code><\/pre>\n\n\n\n
Check Blocking Sessions<\/h2>\n\n\n\n
set pages 100\ncol USERNAME for a20\ncol WAIT_CLASS for a30\nset lines 10000\nselect \nblocking_session, \nsid, \nserial#, \nusername,\nstatus,\nwait_class,\nsql_id,\nseconds_in_wait\nfrom \nv$session\nwhere \nblocking_session is not NULL order by 6;<\/code><\/pre>\n\n\n\n
Find all blocked sessions and who is blocking them<\/h2>\n\n\n\n
select sid,blocking_session,username,sql_id,event,machine,osuser,program,last_call_et from v$session where blocking_session > 0;<\/code><\/pre>\n\n\n\n
select * from dba_blockers\nselect * from dba_waiters<\/code><\/pre>\n\n\n\n
Find what the blocking session is doing<\/h2>\n\n\n\n
select sid,blocking_session,username,sql_id,event,state,machine,osuser,program,last_call_et from v$session where sid=746;<\/code><\/pre>\n\n\n\n
Find the blocked objects<\/h2>\n\n\n\n
select owner,object_name,object_type from dba_objects where object_id in (select object_id from v$locked_object where session_id=540 and locked_mode =10);<\/code><\/pre>\n\n\n\n
Find current running sqls<\/h2>\n\n\n\n
set lines 200 pages 200\nselect sesion.sid,sesion.username,optimizer_mode,hash_value,address,cpu_time,elapsed_time,sql_text \nfrom v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address \nand sesion.username is not null;<\/code><\/pre>\n\n\n\n
Find active sessions in database<\/h2>\n\n\n\n
set echo off \nset lines 10000\nset pages 400\nset linesize 95 \nset head on \nset feedback on \ncol sid head \"Sid\" form 9999 trunc \ncol serial# form 99999 trunc head \"Ser#\" \ncol username form a8 trunc \ncol osuser form a7 trunc \ncol machine form a20 trunc head \"Client|Machine\" \ncol program form a15 trunc head \"Client|Program\" \ncol login form a11 \ncol \"last call\" form 9999999 trunc head \"Last Call|In Secs\" \ncol status form a6 trunc \nselect sid,serial#,sql_id,substr(username,1,10) username,substr(osuser,1,10) osuser, \nsubstr(program||module,1,15) program,substr(machine,1,22) machine, \nto_char(logon_time,'ddMon hh24:mi') login, \nlast_call_et \"last call\",status \nfrom gv$session where status='ACTIVE' \norder by 1 \n\/<\/code><\/pre>\n\n\n\n
Find wait events in database<\/h2>\n\n\n\n
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser, \nsubstr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine, \na.event,a.p1,b.sql_hash_value \nfrom v$session_wait a,V$session b \nwhere b.sid=a.sid \nand a.event not in('SQL*Net message from client','SQL*Net message to client', \n'smon timer','pmon timer') \nand username is not null \norder by 6 \n\/<\/code><\/pre>\n\n\n\n
Find sessions generating undo<\/h2>\n\n\n\n
select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks \nfrom v$session a, v$transaction b \nwhere a.saddr=b.ses_addr;<\/code><\/pre>\n\n\n\n
Find sessions generating lots of redo<\/h2>\n\n\n\n
set lines 2000 \nset pages 1000 \ncol sid for 99999 \ncol name for a09 \ncol username for a14 \ncol PROGRAM for a21 \ncol MODULE for a25 \nselect s.sid,sn.SERIAL#,n.name, round(value\/1024\/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) \"program\", sn.type, sn.module,sn.sql_id \nfrom v$sesstat s join v$statname n on n.statistic# = s.statistic# \njoin v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by \nredo_mb desc;<\/code><\/pre>\n\n\n\n
Check parallel sessions<\/h2>\n\n\n\n
set lines 400 pages 400\ncol sql format a38\ncol username format a21\ncol secs format 999999999\ncol machine format a12\ncol event format a27\ncol state format a10\ncol inst for 9999\nselect \/*+ rule *\/ distinct\nw.inst_id inst,w.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,\nsubstr(q.sql_text,1,35) \"SQL\",round(s.LAST_CALL_ET) SECS\nfrom gv$session_wait w,gv$session s,gv$sql q where \nw.sid=s.sid\nand s.SQL_HASH_VALUE=q.HASH_VALUE\nand s.username is not null\norder by \"SECS\";<\/code><\/pre>\n\n\n\n
Get parallel query details<\/h2>\n\n\n\n
col username for a9 \ncol sid for a8 \nset lines 299 \nselect \ns.inst_id, \ndecode(px.qcinst_id,NULL,s.username, \n' - '||lower(substr(s.program,length(s.program)-4,4) ) ) \"Username\", \ndecode(px.qcinst_id,NULL, 'QC', '(Slave)') \"QC\/Slave\" , \nto_char( px.server_set) \"Slave Set\", \nto_char(s.sid) \"SID\", \ndecode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) \"QC SID\", \npx.req_degree \"Requested DOP\", \npx.degree \"Actual DOP\", p.spid \nfrom \ngv$px_session px, \ngv$session s, gv$process p \nwhere \npx.sid=s.sid (+) and \npx.serial#=s.serial# and \npx.inst_id = s.inst_id \nand p.inst_id = s.inst_id \nand p.addr=s.paddr \norder by 5 , 1 desc \n\/<\/code><\/pre>\n\n\n\n
Monitor parallel queries<\/h2>\n\n\n\n
select \ns.inst_id, \ndecode(px.qcinst_id,NULL,s.username, \n' - '||lower(substr(s.program,length(s.program)-4,4) ) ) \"Username\", \ndecode(px.qcinst_id,NULL, 'QC', '(Slave)') \"QC\/Slave\" , \nto_char( px.server_set) \"Slave Set\", \nto_char(s.sid) \"SID\", \ndecode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) \"QC SID\", \npx.req_degree \"Requested DOP\", \npx.degree \"Actual DOP\", p.spid \nfrom \ngv$px_session px, \ngv$session s, gv$process p \nwhere \npx.sid=s.sid (+) and \npx.serial#=s.serial# and \npx.inst_id = s.inst_id \nand p.inst_id = s.inst_id \nand p.addr=s.paddr \norder by 5 , 1 desc;<\/code><\/pre>\n\n\n\n
Get sid from os pid ( server process)<\/h2>\n\n\n\n
col sid format 999999 \ncol username format a20 \ncol osuser format a15 \nselect b.spid,a.sid, a.serial#,a.username, a.osuser \nfrom v$session a, v$process b \nwhere a.paddr= b.addr \nand b.spid='&spid' \norder by b.spid;<\/code><\/pre>\n\n\n\n
Check long running sessions<\/h2>\n\n\n\n
col SOFAR format 99999999999999999\ncol TOTALWORK format 9999999999999999\ncol username format a12\ncol event format a21\nselect s.sid,s.username,l.SOFAR,l.TOTALWORK,\nround(l.SOFAR*100\/l.TOTALWORK) \"Percent Complete\",\nl.TIME_REMAINING , substr(s.event,1,20) event\nfrom v$session_longops l,v$session s\nwhere round(l.SOFAR*100\/l.TOTALWORK) <> 100\nand s.sid=l.sid\norder by 6;<\/code><\/pre>\n\n\n\n
Find long running operations<\/h2>\n\n\n\n
select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining from gv$session_longops where totalwork<>sofar;<\/code><\/pre>\n\n\n\n
Find blocking sessions that were blocking for more than 15 minutes + objects and sql<\/h2>\n\n\n\n
select s.SID,p.SPID,s.machine,s.username,CTIME\/60 as minutes_locking, do.object_name as locked_object, q.sql_text\nfrom v$lock l\njoin v$session s on l.sid=s.sid\njoin v$process p on p.addr = s.paddr\njoin v$locked_object lo on l.SID = lo.SESSION_ID\njoin dba_objects do on lo.OBJECT_ID = do.OBJECT_ID \njoin v$sqlarea q on  s.sql_hash_value = q.hash_value and s.sql_address = q.address\nwhere block=1 and ctime\/60>15<\/code><\/pre>\n\n\n\n
Check who is blocking who in RAC<\/h2>\n\n\n\n
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type\nFROM gv$lock\nWHERE (id1, id2, type) IN (\nSELECT id1, id2, type FROM gv$lock WHERE request>0)\nORDER BY id1, request;<\/code><\/pre>\n\n\n\n
Check who is blocking who in RAC, including objects<\/h2>\n\n\n\n
SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type\nFROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id\njoin gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id\njoin dba_objects do on lo.OBJECT_ID = do.OBJECT_ID \nWHERE (id1, id2, gv$lock.type) IN (\nSELECT id1, id2, type FROM gv$lock WHERE request>0)\nORDER BY id1, request;<\/code><\/pre>\n\n\n\n
Find Top 25 Wait Events<\/h2>\n\n\n\n
set lines 180\nset pages 1000\ncol event format a50\n\nPROMPT --> Top 25 Wait Events\n\nselect * from (\nselect inst_id,event,count(*) E_COUNT from gv$session_wait\nwhere event <> 'SQL*Net message from client'\ngroup by inst_id,event order by 3 desc)\nwhere rownum < 26\norder by 3\n\/\ncol sql format a35\ncol username format a20\ncol child format 999\ncol secs format 9999\ncol machine format a12\ncol event format a25\ncol state format a10\n\nselect \/*+ rule *\/ distinct\nw.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,--q.CHILD_NUMBER CHILD,\nsubstr(q.sql_text,1,33) \"SQL\",s.WAIT_TIME_MICRO\/1000000 SEC\nfrom gv$session_wait w,gv$session s,gv$sql q where w.event like '%&event%'\nand w.sid=s.sid\nand s.SQL_HASH_VALUE=q.HASH_VALUE(+)\nand s.status='ACTIVE'\nand s.username is not null\nand substr(w.event,1,25) not like 'SQL*Net message from clie%'\norder by \"SEC\"\n\/<\/code><\/pre>\n\n\n\n
Find sessions consuming lot of CPU<\/h2>\n\n\n\n
col program form a30 heading \"Program\" \ncol CPUMins form 99990 heading \"CPU in Mins\" \nselect rownum as rank, a.* \nfrom ( \nSELECT v.sid, program, v.value \/ (100 * 60) CPUMins \nFROM v$statname s , v$sesstat v, v$session sess \nWHERE s.name = 'CPU used by this session' \nand sess.sid = v.sid \nand v.statistic#=s.statistic# \nand v.value>0 \nORDER BY v.value DESC) a \nwhere rownum < 11;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
An Oracle session represents a single connection from a user or application to the database. Monitoring and managing sessions is key for performance, troubleshooting, and resource control. Check Session Details Terminate All Sessions for a Specific SQL_ID FOR RAC- kill all sessions of a sql_id Kill all session of a user Inactive session check Inactive […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-3795","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3795","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3795"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3795"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}