Search This Blog

Total Pageviews

Saturday 3 September 2016

Oracle session count info and with kill statement


Oracle session count info and with kill statement  ..


show parameter spfile
my session

select spid,pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));


spfile info ..

set lines 200
col name  for a30
col value for a70
select i.INSTANCE_NAME "INSTANCE", NAME , VALUE  from gv$instance i, gv$parameter p 
where i.inst_id=p.inst_id and upper(p.name)='SPFILE';

set linesize 300 col SERVICE_NAME for a20 SELECT * FROM ( select inst_id, machine, service_name from gv$session where 1=1 and status ='ACTIVE' -- and service_name like '%oltp%' --group by inst_id, machine, service_name ) PIVOT ( COUNT(inst_id) FOR inst_id IN (1, 2, 3,4) ) ORDER BY machine;

col name  for a20
col VALUE for a15
select INSTANCE_NAME,NAME,VALUE from gv$parameter p, gv$instance i  
where p.INST_ID=i.INST_ID 
and p.name in ('processes', 'sessions')
order by instance_name;

col name   for a20
col VALUE  for a15
col SID    for a10
select INSTANCE_NAME,sp.SID, NAME,VALUE from gv$spparameter sp, gv$instance i
where sp.INST_ID=i.INST_ID 
and sp.name in ('processes', 'sessions')
order by instance_name;

-- Current session info set linesize 300 col LIMIT_VALUE for a15 col "%" for 9999999 col INITIAL_ALLOCATION for a20 col ATTENTION for a30
col RESOURCE_NAME for a20
select INST_ID,RESOURCE_NAME,current_utilization,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE,(ROUND(((CURRENT_UTILIZATION/TO_NUMBER(LIMIT_VALUE)))*100)) "%"
, case when ((ROUND(( (CURRENT_UTILIZATION/to_number(LIMIT_VALUE)) )*100))>85.00) then '---(>85.00)% full ##'
else 'Good'
end as ATTENTION
from gv$resource_limit
WHERE resource_name in ('processes', 'sessions');

 INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE % ATTENTION
---------- ------------------------------ ------------------- --------------- -------------------- --------------- -------- -----------
 1 processes 152 179 600 600 25 Good
 1 sessions 121 153 928 928 13 Good
 2 processes 150 172 600 600 25 Good
 2 sessions 125 151 928 928 13 Good


set linesize 500  pages 500
col sql_text format a100 wrap
col username for a20
col kill for a15
col osuser format a12
col machine format a12
col module format a12
col EVENT for a25
alter session set nls_Date_format='dd-MON-yyyy hh24:mi';
select
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill  ,
	ses.username, ses.status,
	ses.osuser, ses.machine, ses.module, 
	ses.EVENT, ses.logon_time, ses.curr_prev,
	sql.sql_id, sql.child_number, sql.sql_text
from	
	(
	select
		ses1.sid, ses1.serial#, ses1.username, ses1.status,
		ses1.osuser, ses1.machine, ses1.module, 
		'Current' curr_prev,
		ses1.event, ses1.logon_time,
		ses1.sql_id, ses1.sql_child_number, ses1.sql_address,ses1.inst_id
	from	gv$session	ses1
	union all
	select
		ses2.sid, ses2.serial#, ses2.username, ses2.status,
		ses2.osuser, ses2.machine, ses2.module, 
		'Previous' curr_prev,
		ses2.event, ses2.logon_time,
		ses2.prev_sql_id, ses2.prev_child_number, ses2.prev_sql_addr,ses2.inst_id
	from gv$session	ses2
	)		ses,
	gv$sql	sql
where 1=1
and	ses.sql_address != '00'
--and	ses.status	 = 'ACTIVE'
and	sql.sql_id	 = ses.sql_id
and	sql.child_number = ses.sql_child_number
and	sql.inst_id	 = ses.inst_id
and ses.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
order by ses.sid, ses.curr_prev
;

set lines 200 pages 999
col osuser FOR a25
col username FOR a28
col machine FOR a50
col status FOR a9
col min_date FOR a20
col max_date FOR a20
col cnt FOR 9999
break ON status skip 1
compute SUM OF cnt ON status
SELECT inst_id,
       osuser,
       username,
       machine,
       status,
       To_char(Min(logon_time), 'dd-MON-YYYY:HH24:mi:ss') MIN_DATE,
       To_char(Max(logon_time), 'dd-MON-YYYY:HH24:mi:ss') MAX_DATE,
       Count(*) "CNT"
FROM   gv$session
GROUP  BY inst_id,
          osuser,
          username,
          status,
          machine
ORDER  BY status,
          To_char(Min(logon_time), 'dd-MON-YYYY:HH24:mi:ss'),
          To_char(Max(logon_time), 'dd-MON-YYYY:HH24:mi:ss');
		  
		  
		  

set lines 200 pages 999
col OSUSER    for a20
col USERNAME  for a20
col MACHINE   for a30
col STATUS    for a9
col MIN_DATE  for a20
col MAX_DATE  for a20
col CNT       for 999
break on status skip 1
compute sum of CNT on status
SELECT 
inst_id,
OSUSER,
USERNAME,
MACHINE,
STATUS,
to_char(min(logon_time),'dd-MON-YYYY:HH24:mi:ss') MIN_DATE,
to_char(max(logon_time),'dd-MON-YYYY:HH24:mi:ss') MAX_DATE,
/* rpad(COUNT(*),3,' ') "CNT" */
COUNT(*) "CNT"
FROM GV$SESSION
GROUP BY inst_id,OSUSER,USERNAME,STATUS,MACHINE
ORDER BY STATUS,to_char(min(logon_time),'dd-MON-YYYY:HH24:mi:ss'),to_char(max(logon_time),'dd-MON-YYYY:HH24:mi:ss')
/


 INST_ID OSUSER USERNAME MACHINE STATUS MIN_DATE MAX_DATE CNT
---------- -------------------- -------------------- ------------------------------ --------- -------------------- -------------------- ----
 1 oracle SYS **************************** ACTIVE 03-SEP-2016:01:00:19 03-SEP-2016:12:02:39 3
 2 oracle SYS **************************** 03-SEP-2016:12:02:39 03-SEP-2016:12:02:39 1
 1 oracle **************************** 13-JUL-2016:13:42:11 15-AUG-2016:23:03:09 75
 2 oracle **************************** 18-JUL-2016:03:27:13 01-SEP-2016:00:05:06 81
 ********* ----
 sum 160

 1 oracle SYS **************************** INACTIVE 13-JUL-2016:13:42:29 02-SEP-2016:12:05:55 5
 2 oracle SYS **************************** 18-JUL-2016:03:27:33 18-JUL-2016:03:27:33 1
 ********* ----
 sum 6


6 rows selected.



---Total session count on RAC 

select dbname ,active, inactive, system, killed
from (select name dbname     from v$database ) 
 , (select count(*) total    from gv$session)
 , (select count(*) system   from gv$session where type = 'BACKGROUND')
 , (select count(*) active   from gv$session where status = 'ACTIVE' and username is not null)
 , (select count(*) inactive from gv$session where status = 'INACTIVE')
 , (select count(*) killed   from gv$session where status = 'KILLED') ;


DBNAME ACTIVE INACTIVE SYSTEM KILLED
--------- ---------- ---------- ---------- ----------
AJRAC 4 6 157 0


-- session count RAC1
select INSTANCE_NUMBER,INSTANCE_NAME, total,total, active, inactive, system, killed from (select INSTANCE_NUMBER ,INSTANCE_NAME from gv$instance where INST_ID=1) , (select count(*) total from gv$session where INST_ID=1) , (select count(*) system from gv$session where username is null and INST_ID=1) , (select count(*) active from gv$session where status = 'ACTIVE' and username is not null and INST_ID=1) , (select count(*) inactive from gv$session where status = 'INACTIVE' and INST_ID=1) , (select count(*) killed from gv$session where status = 'KILLED' and INST_ID=1) ; INSTANCE_NUMBER INSTANCE_NAME TOTAL TOTAL ACTIVE INACTIVE SYSTEM KILLED --------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- 1 AJRAC1 82 82 2 5 75 0
--session count RAC2
select INSTANCE_NUMBER,INSTANCE_NAME,total, active, inactive, system, killed
from (select INSTANCE_NUMBER ,INSTANCE_NAME from gv$instance where INST_ID =2 )
 , (select count(*) total    from gv$session where INST_ID =2 )
 , (select count(*) system   from gv$session where username is null and INST_ID=2)
 , (select count(*) active   from gv$session where status = 'ACTIVE' and username is not null and INST_ID=2)
 , (select count(*) inactive from gv$session where status = 'INACTIVE' and INST_ID=2)
 , (select count(*) killed   from gv$session where status = 'KILLED' and INST_ID=2);


INSTANCE_NUMBER INSTANCE_NAME TOTAL ACTIVE INACTIVE SYSTEM KILLED
--------------- ---------------- ---------- ---------- ---------- ---------- ----------
 2 AJRAC2 83 1 1 81 0

With Union 
set linesize 300
compute sum of cnt on report
compute sum of INACTIVE on report
compute sum of ACTIVE on report
compute sum of killed on report
compute sum of SYSTEM  on report
 break on report
set linesize 300
select INSTANCE_NUMBER,INSTANCE_NAME, total, active, inactive, system, killed
from (select INSTANCE_NUMBER ,INSTANCE_NAME from gv$instance where INST_ID=1)
 , (select count(*) total    from gv$session where INST_ID=1)
 , (select count(*) system   from gv$session where username is null and INST_ID=1)
 , (select count(*) active   from gv$session where status = 'ACTIVE' and username is not null and INST_ID=1)
 , (select count(*) inactive from gv$session where status = 'INACTIVE' and INST_ID=1)
 , (select count(*) killed   from gv$session where status = 'KILLED' and INST_ID=1) 
union all 
--session count RAC2
select INSTANCE_NUMBER,INSTANCE_NAME,total, active, inactive, system, killed
from (select INSTANCE_NUMBER ,INSTANCE_NAME from gv$instance where INST_ID =2 )
 , (select count(*) total    from gv$session where INST_ID =2 )
 , (select count(*) system   from gv$session where username is null and INST_ID=2)
 , (select count(*) active   from gv$session where status = 'ACTIVE' and username is not null and INST_ID=2)
 , (select count(*) inactive from gv$session where status = 'INACTIVE' and INST_ID=2)
 , (select count(*) killed   from gv$session where status = 'KILLED' and INST_ID=2);

col kill for a70
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill,username,sql_id from GV$SESSION 
where 1=1
-- and username='username'
and status = 'INACTIVE'
and type != 'BACKGROUND'
--and USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ;


-- to make a kill session script 

set heading off feedback off 
col kill for a70
spool kill.sql
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill from GV$SESSION 
where 1=1
-- and username='user'
and status = 'INACTIVE'
and type != 'BACKGROUND'
--and USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ;
spool off
set heading on feedback on 


===
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 500 pagesize 100
clear columns
col username    for a15
col osuser 		for a16
col program 	for a10 trunc
col Locked 		for a6
col status 		for a1 trunc print
col "hh:mm:ss" 	for a8
col SQL_ID 		for a15
col seq# 		for 99990 --Sequence number that uniquely identifies the wait. Incremented for each wait
col module 		for a25
col ACTION 		for a20
col kill 		for a15
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
select ''''||sid ||','|| serial#||',@'||inst_id ||''''  kill
--,con_id --12c
, username
, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser
, substr(program,instr(program,'/',-1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program,
 decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , sql_id,prev_sql_id, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state
, substr(module,1,25) module,final_blocking_session_status,sql_exec_start
-- substr(action,1,20) action
from GV$SESSION
where type = 'USER'
and STATUS!='INACTIVE'
and audsid != 0 -- to exclude internal processess
--and USERNAME='USERNAME'
--and SQL_ID='1mavr84vthn3s'
-- and s.event like 'enq: TX%'
order by inst_id, status, last_call_et desc, sid
/

==

set linesize 300  pagesize 80
col INST_ID 			for 99
col spid 				for a10
col PROGRAM 			for a25
col action 				for a10
col logon_time 			for a16
col module 				for a25
col cli_process 		for a7
col cli_mach 			for a15
col status 				for a10
col username 			for a10
col last_call_et 		for 9999.99
col sql_hash_value 		for 9999999999999
col username 			for a10
col kill for a15
col s.username for a15
col "Last SQL" for a60
col elapsed_time for 999999999999
select distinct ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.username,
 -- p.spid,
 s.last_call_et/3600 last_call_et ,s.sql_id,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,50) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address 
and s.sql_hash_value =t.hash_value 
and p.addr=s.paddr 
and s.status!='INACTIVE'
--and s.program='JDBC Thin Client'
--and s.last_call_et > 3600
and s.sid not in (select sid from V$SESSION where AUDSID = userenv('SESSIONID') )
and s.username not in ('SYS')
order by last_call_et;

====
Active Sql_id with sql text 

set pages 400 lines 500
col host_name 	for a20
col event 		for a40
col machine 	for a30
col sql_text 	for a50
col username 	for a15
col kill 		for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill,a.sql_id,s.username
--,i.host_name
,machine,s.event,s.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login,a.sql_text
from gv$session s,gv$sqlarea a,gv$instance i
where s.username is not null
and s.status='ACTIVE'
and s.sql_address=a.address
and s.inst_id=a.inst_id 
and i.inst_id = a.inst_id
--and sql_text not like '%from gv$session s,gv$sqlarea a,gv$instance i%'
and  ( s.sid,s.serial#) not in (SELECT sid, serial# FROM SYS.V_$SESSION  WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT))
/


===========
-- Oracle 12C session info 

set linesize 300 pagesize 300
--set pagesize 0 set linesize 300
col kill     for a60
col USERNAME for a12
col PDB_NAME for a15
col EVENT    for a50
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ; -- ' kill,username,s.con_id,pdb_name,sql_id,event from gv$session s ,dba_pdbs p
where 1=1
-- and username='username'
-- and status = 'INACTIVE'
-- and USERNAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
and type != 'BACKGROUND'
and  s.con_id=p.CON_ID(+);


KILL                                                         USERNAME         CON_ID PDB_NAME        SQL_ID        EVENT
------------------------------------------------------------ ------------ ---------- --------------- ------------- --------------------------------------------------
alter system kill session '34,44188,@1' immediate ; --       ANUJ                  4 ORDS                          SQL*Net message from client
alter system kill session '61,56196,@1' immediate ; --       SYS                   1                 59p1yadp2g6mb Streams AQ: waiting for messages in the queue
alter system kill session '68,3229,@1' immediate ; --        SYS                   1                 59p1yadp2g6mb Streams AQ: waiting for messages in the queue
alter system kill session '73,53368,@1' immediate ; --       SYS                   1                 3t479j8hhqt0t SQL*Net message to client


to remove everything after ";" !!!! 
sed 's/;.*/;/' out.txt
=====================================

To Kill Pluggable database ( ANUJ)  session only

set linesize 300 pagesize 300
--set pagesize 0 set linesize 300
col kill     for a60
col USERNAME for a12
col PDB_NAME for a15
col EVENT    for a50
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ; -- ' kill,username,s.con_id,pdb_name,sql_id,event from gv$session s ,dba_pdbs p
where 1=1
-- and username='username'
-- and status = 'INACTIVE'
-- and USERNAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
and type != 'BACKGROUND'
and  s.con_id=p.CON_ID
and pdb_name='ANUJ'; -----<<<<<<<<<<Pluggable database name


--Session count from history 

set linesize 200 pagesize 200
col SNAP_BEGIN format a20
SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
r.instance_number instance,
r.current_utilization sessions 
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
( 
--Select the Maximum of the Snapshot IDs within an hour if all of the snapshot IDs
--have the same number of sessions
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-4)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-4)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
ORDER BY snap_begin,instance


SNAP_BEGIN INSTANCE SESSIONS
-------------------- ---------- ----------
01-SEP-2016 00:00:00 1 131
01-SEP-2016 00:00:00 2 130
01-SEP-2016 01:00:00 1 129
01-SEP-2016 01:00:00 2 131
01-SEP-2016 02:00:00 1 127
01-SEP-2016 02:00:00 2 129
01-SEP-2016 03:00:00 1 126
01-SEP-2016 03:00:00 2 129
01-SEP-2016 04:00:00 1 122
01-SEP-2016 04:00:00 2 129
01-SEP-2016 05:00:00 1 127
01-SEP-2016 05:00:00 2 129
01-SEP-2016 06:00:00 1 125
01-SEP-2016 06:00:00 2 129





alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set linesize 300 pagesize 200
col EVENT    for a30
col USERNAME for a20
col KILL     for a52
select 
 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill,
 -- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
 s.status status,
 s.username ,
 s.logon_time "Logon Time",
 s.sql_id,
 sysdate - s.last_call_et/86400 latime,
 round((sysdate-s.logon_time)*1440) etime,
 s.last_call_et ,
 floor(last_call_et / 60*60 ) "Hr since active" ,
 (last_call_et / 60*60*24 ) "Days since active" ,
 ROUND((SYSDATE - LOGON_TIME)*(24*60),1) LOGON_TIME,
 EVENT
from gv$session s
where 1=1 
and s.username is not null
-- and ROUND((SYSDATE - LOGON_TIME)*(24*60),1) > 60*24 --- 1 hr
and last_call_et/60*60*24 > 1 ---- min*hr*24 one day
and s.type != 'BACKGROUND'
-- and USERNAME='ANUJ'
order by 3, 7, 8;




KILL STATUS USERNAME Logon Time SQL_ID LATIME ETIME LAST_CALL_ET Hr since active Days since active LOGON_TIME EVENT
---------------------------------------------------- --------- -------------------- ---------------- ------------- ---------------- ---------- ------------ --------------- ----------------- ---------- ------------------------------
alter system kill session '183,25864,@1' immediate ; INACTIVE SYS 02-09-2016 12:05 03-09-2016 12:58 1511 1090 1090 26160 1511.1 SQL*Net message from client
alter system kill session '239,31544,@1' immediate ; SYS 26-08-2016 10:24 26-08-2016 10:59 11692 699437 699436 16786488 11692.3 SQL*Net message from client
alter system kill session '407,56366,@2' immediate ; SYS 18-07-2016 03:27 03-09-2016 13:17 68270 1 1 24 68269.5 SQL*Net message from client
alter system kill session '643,63640,@1' immediate ; SYS 13-07-2016 13:44 18-07-2016 03:27 74853 4096170 4096170 98308080 74852.9 SQL*Net message from client
alter system kill session '877,35540,@1' immediate ; SYS 13-07-2016 13:42 03-09-2016 13:16 74855 9 9 216 74854.6 SQL*Net message from client
alter system kill session '293,50579,@1' immediate ; SYS 13-07-2016 13:42 03-09-2016 13:12 74855 272 272 6528 74854.6 SQL*Net message from client


set serveroutput on size 100000 echo off feedback off lines 300

declare

 cursor sessinfo is
 SELECT NVL(s.username, '(oracle)') AS username,
 s.osuser,
 s.sid,
 s.serial#,
 p.spid,
 s.status,
 s.module,
 s.machine,
 s.program,
 TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
 s.last_call_et/3600 last_call_et_Hrs,
 lpad(t.sql_text,30) "Last SQL"
 from gv$session s,gv$sqlarea t, gv$process p 
 where s.sql_address = t.address and 
 s.sql_hash_value =t.hash_value 
 and p.addr=s.paddr 
-- and s.status='INACTIVE' -------<<<<<<<<<<<<<
 and s.last_call_et > (3600) 
 and s.type != 'BACKGROUND'
order by last_call_et;

 sess sessinfo%rowtype;

 sql_string1 Varchar2(2000);
 sql_string2 Varchar2(2000);


begin

 open sessinfo;

 loop
 fetch sessinfo into sess;
 exit when sessinfo%notfound;

 sql_string1 := ' sid= ' || sess.sid ||
 ' serail#= ' || sess.serial# ||
 ' machine= ' || sess.machine ||
 ' program= ' || sess.program ||
 ' username= ' || sess.username ||
 ' Inactive_Hr=' || trunc(sess.last_call_et_Hrs) || 
 ' OS_USER= ' || sess.osuser;

 sql_string2 := 'alter system kill session ' || chr(39) || sess.sid || ',' || chr(39) || sess.serial# || ';';


 dbms_output.put_line(sql_string1);
 dbms_output.put_line(sql_string2);
 end loop;

 close sessinfo;
end;
/


sid= 761 serail#= 7301 machine= *********************************= oracle@********************************* (W00E) username= (oracle) Inactive_Hr=1061 OS_USER= oracle
alter system kill session '761,'7301;
sid= 703 serail#= 54582 machine= *********************************= oracle@********************************* (W00D) username= (oracle) Inactive_Hr=1069 OS_USER= oracle
alter system kill session '703,'54582;
sid= 703 serail#= 54582 machine= *********************************= oracle@********************************* (W00D) username= (oracle) Inactive_Hr=1069 OS_USER= oracle
alter system kill session '703,'54582;

=====
col Instance for a40
col  Machine for a45	
select gi.iname "Instance", case grouping(gi.iname) when 1 then 'Total' else case grouping(gs.machine) 
when 1 then 'Total '||gi.iname else gs.machine end end "Machine", count(1) "Sessions" 
from gv$session gs, ( select inst_id, instance_name||'@'||host_name iname from gv$instance ) gi 
where gs.username is not null 
and gs.inst_id = gi.inst_id 
group by rollup(gi.iname,gs.machine)

============

Session information on known SID basis

set echo off linesize 132 verify off feedback off serveroutput on
declare
 SID number := 0 ;
 SERIAL number := 0 ;
 username varchar(20) := '';
 Status varchar(8) := '';
 machine varchar(10) := '';
 terminal varchar(25) := '';
 program varchar(30) := '';
 Module varchar(30) := '';
 Action varchar(20) := '';
 sql_hash_value number := 0 ;
 logontime varchar(30) := '';
 last_call_et number := 0 ;
 proc number := 0 ;
 spid number := 0 ;
 event varchar(30) := '';
 state varchar(30) := '';
 sql_text varchar(1000) := '';
cursor cur1 is
select
 a.sid sid,
 a.serial# serial,
 a.username username,
 a.status status ,
 a.machine machine,
 a.terminal terminal,
 a.program program,
 a.module module,
 a.action action,
 a.sql_hash_value sql_hash_value,
 to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
 a.last_call_et last_call_et,
 a.process proc,
 b.spid spid,
 sw.event event,
 sw.state state
 from gv$session a, gv$process b, gv$session_wait sw
 where a.paddr=b.addr
 and a.inst_id=b.inst_id
 and a.sid ='&sid'
 and a.inst_id=sw.inst_id
 and a.sid=sw.sid;
begin
 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');
 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
 for m in cur1 loop
 DBMS_OUTPUT.ENABLE(50000);
 DBMS_OUTPUT.PUT_LINE(' ');
 DBMS_OUTPUT.PUT_LINE('SID............ : ' || m.sid);
 DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial);
 DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username);
 DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status);
 DBMS_OUTPUT.PUT_LINE('Machine........ : ' || m.machine);
 DBMS_OUTPUT.PUT_LINE('Terminal....... : ' || m.terminal);
 DBMS_OUTPUT.PUT_LINE('Program........ : ' || m.program);
 DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module);
 DBMS_OUTPUT.PUT_LINE('Action......... : ' || m.action);
 DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value);
 DBMS_OUTPUT.PUT_LINE('Logon Time..... : ' || m.logontime);
 DBMS_OUTPUT.PUT_LINE('Last Call Et... : ' || m.last_call_et);
 DBMS_OUTPUT.PUT_LINE('Process ID..... : ' || m.proc);
 DBMS_OUTPUT.PUT_LINE('SPID........... : ' || m.spid);
 DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event);
 DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
 for rec in ( select distinct(sql_text) sql_text from v$session s,v$sql v where
 s.sql_hash_value=v.hash_value and s.sql_address=v.address and s.sid=m.sid)
 loop
 dbms_output.put_line(substr('SQL_TEXT is..........:'||rec.sql_text,1,255));
 end loop;
 DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' ');
 end loop;
end;
/


set echo off linesize 132 verify off feedback off serveroutput on
declare
 SID      number := 0 ;
 SERIAL   number := 0 ;
 username varchar(20) := '';
 Status   varchar(8) := '';
 machine  varchar(10) := '';
 terminal varchar(25) := '';
 program  varchar(30) := '';
 Module   varchar(30) := '';
 Action   varchar(20) := '';
 sql_hash_value number := 0 ;
 logontime varchar(30) := '';
 last_call_et number := 0 ;
 proc number := 0 ;
 spid number := 0 ;
 event varchar(30) := '';
 state varchar(30) := '';
 sql_text varchar(1000) := '';
cursor cur1 is
select
 a.sid sid,
 a.serial# serial,
 a.username username,
 a.status status ,
 a.machine machine,
 a.terminal terminal,
 a.program program,
 a.module module,
 a.action action,
 a.sql_hash_value sql_hash_value,
 to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
 a.last_call_et last_call_et,
 a.process proc,
 b.spid spid,
 b.event event,
 b.state state
 from gv$session a, gv$process b, gv$session_wait sw
 where a.paddr=b.addr
 and a.inst_id=b.inst_id
 and b.spid='&1'
 and a.inst_id=sw.inst_id
 and a.sid=sw.sid;
begin
 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');
 DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
 for m in cur1 loop
 DBMS_OUTPUT.ENABLE(50000);
 DBMS_OUTPUT.PUT_LINE(' ');
 DBMS_OUTPUT.PUT_LINE('SID............ : ' || m.sid);
 DBMS_OUTPUT.PUT_LINE('SERIAL#........ : ' || m.serial);
 DBMS_OUTPUT.PUT_LINE('USERNAME....... : ' || m.username);
 DBMS_OUTPUT.PUT_LINE('STATUS......... : ' || m.status);
 DBMS_OUTPUT.PUT_LINE('Machine........ : ' || m.machine);
 DBMS_OUTPUT.PUT_LINE('Terminal....... : ' || m.terminal);
 DBMS_OUTPUT.PUT_LINE('Program........ : ' || m.program);
 DBMS_OUTPUT.PUT_LINE('Module......... : ' || m.module);
 DBMS_OUTPUT.PUT_LINE('Action......... : ' || m.action);
 DBMS_OUTPUT.PUT_LINE('SQL Hash Value. : ' || m.sql_hash_value);
 DBMS_OUTPUT.PUT_LINE('Logon Time..... : ' || m.logontime);
 DBMS_OUTPUT.PUT_LINE('Last Call Et... : ' || m.last_call_et);
 DBMS_OUTPUT.PUT_LINE('Process ID..... : ' || m.proc);
 DBMS_OUTPUT.PUT_LINE('SPID........... : ' || m.spid);
 DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event);
 DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
 DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' ');
 end loop;
end;
/
===

col kill for a100
-- Kill all sessions specific events 
select       'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill
,'-- ' ,username,s.con_id,sql_id,event
from gv$session s
where 1=1 
-- and (event='latch: shared pool' or event='library cache lock') 
--and s.USERNAME='DBSNMP'
-- and s.sql_id='0vj44a7drw1rj'
--and s.USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) 
and s.USERNAME is not null 
--and CON_ID !=0
;

===
set lines 300 pagesize 200
col MACHINE for a35                                                     
col PROGRAM for a30
select sum(case when status = 'ACTIVE' then 1 else 0 end) as ACTIVE_COUNT
 , sum(case when status = 'INACTIVE' then 1 else 0 end) as INACTIVE_COUNT
 , count(*) as TOTAL_COUNT
 , username, machine,osuser,program
 from gv$session
 where type <> 'BACKGROUND' 
 and username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
 group by username, machine,osuser,program
 order by username, machine,osuser,program;
==========

to connect pluggable database ...

sqlplus "system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj)))"

=========
select 'alter system kill session ' || '''' || sid || ',' || serial# ||',@'|| inst_id || '''' || ' immediate;' from gv$session where sid='&sid';


====
to kill processes 
select 'echo '||inst_id||';kill -9 '||spid from gv$process where (addr,inst_id) in (select paddr,inst_id from gv$session where module like '%ANUJ%') order by inst_id;
===============
Session Info ....



SET    PAGES   0       -
       NEWP    0       -
       LINES   150     -
       TERM    Off     -
       HEAD    Off

Col     X form a150
--spool  all_session.log
TTitle  T Skip 2

select  ' ' x
, lpad( 'SADDR', 24, ' ')			||' : '||s.saddr x
, lpad( '_', 43, '_') x
, lpad( 'SID', 24, ' ')				||' : '||s.sid x
, lpad( 'Sid_serial', 24, ' ')			||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x
, lpad( 'SERIAL#', 24, ' ')			||' : '||s.serial# x
, lpad( 'AUDSID', 24, ' ')			||' : '||s.audsid x
, lpad( 'PADDR', 24, ' ')			||' : '||s.paddr x
, lpad( 'USER#', 24, ' ')			||' : '||s.user# x
, lpad( 'USERNAME', 24, ' ')			||' : '||s.username x
, lpad( 'COMMAND', 24, ' ')			||' : '||s.command x
, lpad( 'TADDR', 24, ' ')			||' : '||s.taddr x
, lpad( 'LOCKWAIT', 24, ' ')			||' : '||s.lockwait x
, lpad( 'STATUS', 24, ' ')			||' : '||s.status x
, lpad( 'SERVER', 24, ' ')			||' : '||s.server x
, lpad( 'SCHEMA#', 24, ' ')			||' : '||s.schema# x
, lpad( 'SCHEMANAME', 24, ' ')			||' : '||s.schemaname x
, lpad( 'OSUSER', 24, ' ')			||' : '||s.osuser x
, lpad( 'Client Process', 24, ' ')		||' : '||s.process x
, lpad( 'Server Process', 24, ' ')		||' : '||p.spid x
, lpad( 'MACHINE', 24, ' ')			||' : '||s.machine x
, lpad( 'TERMINAL', 24, ' ')			||' : '||s.terminal x
, lpad( 'PROGRAM', 24, ' ')			||' : '||s.program x
, lpad( 'TYPE', 24, ' ')			||' : '||s.type x
, lpad( 'SQL_ID', 24, ' ')			||' : '||s.sql_id x
, lpad( 'PREV_SQL_ID', 24, ' ')			||' : '||s.prev_sql_id x
, lpad( 'SQL_ADDRESS', 24, ' ')			||' : '||s.sql_address x
, lpad( 'SQL_HASH_VALUE', 24, ' ')		||' : '||s.sql_hash_value x
, lpad( 'PREV_SQL_ADDR', 24, ' ')		||' : '||s.prev_sql_addr x
, lpad( 'PREV_HASH_VALUE', 24, ' ')		||' : '||s.prev_hash_value x
, lpad( 'MODULE', 24, ' ')			||' : '||s.module x
, lpad( 'MODULE_HASH', 24, ' ')			||' : '||s.module_hash x
, lpad( 'ACTION', 24, ' ')			||' : '||s.action x
, lpad( 'ACTION_HASH', 24, ' ')			||' : '||s.action_hash x
, lpad( 'CLIENT_INFO', 24, ' ')			||' : '||s.client_info x
, lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ')        ||' : '||s.fixed_table_sequence x
, lpad( 'ROW_WAIT_OBJ#', 24, ' ')		||' : '||s.row_wait_obj# x
, lpad( 'ROW_WAIT_FILE#', 24, ' ')		||' : '||s.row_wait_file# x
, lpad( 'ROW_WAIT_BLOCK#', 24, ' ')		||' : '||s.row_wait_block# x
, lpad( 'ROW_WAIT_ROW#', 24, ' ')		||' : '||s.row_wait_row# x
, lpad( 'LOGON_TIME', 24, ' ')			||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x
, lpad( 'LAST_CALL_ET', 24, ' ')		||' : '||s.last_call_et x
, lpad( 'SQL FIRST_LOAD_TIME', 24, ' ')	        ||' : '||sql.first_load_time x
, lpad( 'FINAL_BLOCKING* ..', 24, ' ')	        ||' : '||s.final_blocking_session_status x
, lpad( 'EVENT ..', 24, ' ')			||' : '||s.event x
, lpad( 'SQL', 24, ' ')				||' : '||sql.sql_text x
from    gv$sqlarea sql,gv$process p,gv$session s
where 1=1
and     p.addr             = s.paddr
and	    p.inst_id          = s.inst_id
and     nvl( s.sql_address, 'FFFFFFFF') = sql.Address
and     s.inst_id = sql.inst_id
And     Nvl( s.SERIAL#, 0) > 1
--and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
-- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
/


============




SET LINES 300 pagesize 300
col kill for a15
col UNAM2 format a20  word  heading "Oracle User/OS User"
col PROG format a15 word heading "Program|Terminal"
col SID  format a10 word heading "SID/|Serial#"
col STAT format A7 Heading "Status"
col LTIM format a20 word heading "Logon Time/|Last Activy time"
col TIMI format a20 word heading "Start time/|Last Update time|remaining->elapsed|[%done end time]"
col STMT format a64 heading "Statement" WORD
col OPNA format a10 WORD PRINT HEADING "Long Op"
col WORK format a12 WORD PRINT HEADING "Target|So far|Total Work"
col MESS format a50 WORD PRINT HEADING "Long op message" wrap
--NEWLINE
SELECT   
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill
,ses.sql_id
,lo.opname opna
        ,NVL(ses.username,lo.username)||' / '||ses.osuser||DECODE(ses.audsid,userenv('SESSIONID'),'**','')  unam2
        ,NVL(ses.status,'unknown') stat
--      ,TO_CHAR(ses.logon_time,'DD-MON-YYYY:HH24:MI:SS')||'  '
--        ||to_char(sysdate - (ses.last_call_et)/86400,'DD-MON-YYYY:hh24:mi:ss') ltim
         ,NVL(LPAD(TO_CHAR(target),11,'_'),'___________')||','
         ||LPAD(TO_CHAR(sofar),11,'_')||','
         ||LPAD(TO_CHAR(totalwork),11,'_')||' ('||units||') ['
         ||DECODE(0,totalwork,'n/a',CEIL(100*sofar/(totalwork)))||'%]'
         work
        ,start_time||' '
         ||last_update_time
         ||' '||TO_CHAR(time_remaining)||''
         ||'->'||elapsed_seconds||' ['||
         DECODE(0,time_remaining+elapsed_seconds,'n/a',CEIL(100*elapsed_seconds/(time_remaining+elapsed_seconds)))||'% eta '
         ||TO_CHAR(sysdate+(time_remaining/(60*60*24)),'HH24:MIpm')||']' timi
        ,DECODE(ses.program,'','',ses.program||' '||ses.terminal)  prog
--      ||'('||RTRIM(ses.machine)||')'
--        ,lo.sid||'/'||lo.serial#||' ['||ses.audsid||']' sid
        ,message mess
		,REPLACE(s.sql_text,CHR(10),' ') stmt
--,context
FROM     gv$sql s   ,gv$session_longops lo  ,gv$session ses
WHERE   lo.sql_address=s.address
and     lo.inst_id=s.inst_id
AND     lo.sql_hash_value=s.hash_value
AND     lo.time_remaining!=0
AND     lo.sid=ses.sid(+)
AND     lo.serial#=ses.serial#(+)
/


===================

Kill Script 
 
 set serveroutput on long 99999 lines 131 pages 2000 feedback off head off pagesize 0 linesize 200 long 99999
 
DECLARE
B_USERNAME 		GV$SESSION.USERNAME%TYPE;
B_SID 			GV$SESSION.SID%TYPE;
B_SERIAL 		GV$SESSION.SERIAL#%TYPE;
B_INST_ID 		GV$SESSION.INST_ID%TYPE;
B_LAST_CALL_ET 	GV$SESSION.LAST_CALL_ET%TYPE;
B_SQL_ID 		GV$SESSION.SQL_ID%TYPE;
B_SQLFULLTEXT 	GV$SQLAREA.SQL_FULLTEXT%TYPE;
 
/** This query will pick all the long running sessions **/
 
CURSOR LONGRUNNING_SESS
IS
SELECT distinct s.username,Q.SQL_ID,round(s.last_call_et / 60,2) Time_in_mins,s.sid,s.serial#,s.inst_id
    FROM gv$session s
         JOIN gv$sqltext_with_newlines q ON s.sql_address = q.address
   WHERE 1=1
  -- and status = 'ACTIVE' 
   --AND username like '%ANUJ%' 
   AND TYPE <> 'BACKGROUND'
ORDER BY s.inst_id;
cursor killcurs(v_username in varchar, v_sid in number) 
is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,
event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN 
FROM gv$session 
where username is not null 
and username=v_username 
and sid=v_sid 
order by inst_id;
 
BEGIN
        BEGIN
                OPEN LONGRUNNING_SESS;
                LOOP
                FETCH LONGRUNNING_SESS INTO B_USERNAME,B_SQL_ID,B_LAST_CALL_ET,B_SID,B_SERIAL,B_INST_ID;
                EXIT WHEN LONGRUNNING_SESS%NOTFOUND;
                                IF B_LAST_CALL_ET >=1  --(1 Minutes for this example)  <<<<<<<<<<<<<<<<<<<<
                                THEN
                                        FOR v_rec in killcurs(B_USERNAME,B_SID)
                                        LOOP
                                        IF killcurs%ROWCOUNT > 0
                                        THEN
                                        DBMS_OUTPUT.PUT_LINE('********************************************************');
                                        DBMS_OUTPUT.PUT_LINE(' Started Executing to KILL LONG RUNNING Session : '||v_rec.SID|| CHR(10));
                                        dbms_output.put_line('Long Running Session details --> B_USERNAME - '||B_USERNAME||' B_SQL_ID - '||B_SQL_ID
                                        ||' B_LAST_CALL_ET - '||B_LAST_CALL_ET||' B_SID - '||B_SID||' B_SERIAL - '||B_SERIAL
                                        ||' B_INST_ID - '||B_INST_ID);
                                        DBMS_OUTPUT.PUT_LINE('********************************************************');
                                        SELECT SQL_FULLTEXT INTO B_SQLFULLTEXT from GV$SQLAREA WHERE SQL_ID=B_SQL_ID and INST_ID=B_INST_ID;
                                        DBMS_OUTPUT.PUT_LINE('**** SQL TEXT *****');
                                        DBMS_OUTPUT.PUT_LINE(B_SQLFULLTEXT);
                                        DBMS_OUTPUT.PUT_LINE('********************************************************');
          dbms_output.put_line('alter system kill session '''||v_rec.SID|| ',' || v_rec.SERIAL||',@'||v_rec.inst_id
                || ''' immediate; '||v_rec.username||' '||v_rec.OTIME||' '|| v_rec.event||' '||v_rec.status||' '||v_rec.machine||' '||v_rec.OMIN);
        --   EXECUTE IMMEDIATE 'alter system kill session '''||v_rec.SID|| ',' || v_rec.SERIAL||',@'||v_rec.inst_id||'''immediate ;'  ;
                                        DBMS_OUTPUT.PUT_LINE('********************************************************');
                                        DBMS_OUTPUT.PUT_LINE('Ended Executing to KILL LONG RUNNING Session : '||B_SQL_ID|| CHR(10));
                                        DBMS_OUTPUT.PUT_LINE('********************************************************');
                                        END IF;
                                        END LOOP;
                                END IF;
                DBMS_OUTPUT.PUT(CHR(10));
                DBMS_OUTPUT.PUT(CHR(10));
                END LOOP;
                CLOSE LONGRUNNING_SESS;
                EXCEPTION
                WHEN OTHERS THEN
               dbms_output.put_line('Error : '|| dbms_utility.format_error_stack() || CHR(10) || dbms_utility.format_error_backtrace());
        END;
end;
 
/
       
==================
 
set lines 300 pagesize 300 long 50000
clear column
col username         	for A20 WRAP
col prog       			for A40 WRAP
col run_time         	for A10 JUSTIFY RIGHT
col sid 				for A4  NEW_VALUE sid
col status           	for A16
col kill 				for a15
col "Event/P1/P2/P3" 	for a70
-- ACCEPT search_string PROMPT "Search for: "
SELECT  ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  as kill
,       s.username || chr(10) || s.osuser AS username
,       s.status   || chr(10) || 'PID:' || p.spid AS status
,       lpad( to_char( trunc(24*(sysdate-s.logon_time))) || to_char( trunc(sysdate) + (sysdate-s.logon_time),':MI:SS' ), 10, ' ') AS run_time
,       s.program AS prog
--||  chr(10) || s.event AS prog_event
, s.sql_id,PREV_SQL_ID,s.type,BLOCKING_SESSION_STATUS,EVENT||' | '||P1TEXT||'='||ltrim(to_char(P1,'999999999999'))||decode(P2,0,'',P1,'=P1',' | '||P2TEXT||'='||ltrim(to_char(P2,'999999999999')))
||decode(P3,0,'',' | '||P3TEXT||'='||ltrim(to_char(P3,'999999999999'))) "Event/P1/P2/P3"
FROM   gv$session s  JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE   1=1
-- and schemaname not in ('DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- and s.type != 'BACKGROUND'
AND     audsid != sys_context('USERENV','SESSIONID')
/* AND     upper(  s.osuser || '|' ||
                s.program  || '|' ||
                s.event    || '|' ||
                s.sid      || '|' ||
                s.username || '|' ||
                p.spid
        ) LIKE upper('%search_string.%') */
ORDER BY  3
/ 

=========================
-- With waits 
set linesize 500 pagesize 300
col event  			for a30
col client_identifier  	        for a10 
col wait_class  		for a10 
col module 			for a10 
col program  			for a10 
col kill 			for a15
col action 			for a10 
col machine 			for a30
col osuser 			for a15 
col username  			for a14
select 
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.username,
 s.status,
(select value from gv$sesstat natural join v$statname where name = 'physical reads' and sid=s.sid and INST_ID=s.inst_id)disk_reads,
(select value from gv$sesstat natural join v$statname where name = 'session logical reads' and sid=s.sid and INST_ID=s.inst_id) logical_reads,
 (select value/100 cpu_sec from gv$sesstat natural join v$statname where name = 'CPU used by this session' and sid=s.sid and INST_ID=s.inst_id)cpu_sec,
 s.logon_time,
 --s.program,
 s.osuser,
 s.machine,
-- p.pid,
-- p.spid,
 s.client_identifier,
-- s.client_info,
 s.module,
 s.action,
 s.event,
 s.wait_class,
 case
 when s.status = 'ACTIVE' then
 s.last_call_et
 else
 null
 end wait_sec,
 s.blocking_session_status,
 s.blocking_instance,
 s.blocking_session,
 s.sql_id,
 s.sql_hash_value,
 s.sql_address,
 s.prev_sql_id,
 s.prev_hash_value,
 s.prev_sql_addr,
 s.sql_child_number,
 s.prev_child_number,
 s.server
 from gv$session s join gv$process p on (s.inst_id = p.inst_id and s.paddr = p.addr)
 where s.type = 'USER' 
 and s.status='ACTIVE'
and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS')
order by 6;

-- with pdb
set linesize 500 pagesize 300
col event  			for a30
col client_identifier  	        for a10 
col wait_class  		for a10 
col module 			for a10 
col program  			for a10 
col kill 			for a15
col action 			for a10 
col machine 			for a30
col osuser 			for a15 
col username  			for a14
col pdb_name for a15
select 
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.username,
 s.status,
(select value from gv$sesstat natural join v$statname where name = 'physical reads' and sid=s.sid and INST_ID=s.inst_id)disk_reads,
(select value from gv$sesstat natural join v$statname where name = 'session logical reads' and sid=s.sid and INST_ID=s.inst_id) logical_reads,
 (select value/100 cpu_sec from gv$sesstat natural join v$statname where name = 'CPU used by this session' and sid=s.sid and INST_ID=s.inst_id)cpu_sec,
s.con_id,
(select distinct NAME from gv$pdbs where CON_ID=s.con_id) pdb_name,
 s.logon_time,
 --s.program,
 s.osuser,
 s.machine,
-- p.pid,
-- p.spid,
 s.client_identifier,
-- s.client_info,
 s.module,
 s.action,
 s.event,
 s.wait_class,
 case
 when s.status = 'ACTIVE' then
 s.last_call_et
 else
 null
 end wait_sec,
 s.blocking_session_status,
 s.blocking_instance,
 s.blocking_session,
 s.sql_id,
 s.sql_hash_value,
 s.sql_address,
 s.prev_sql_id,
 s.prev_hash_value,
 s.prev_sql_addr,
 s.sql_child_number,
 s.prev_child_number,
 s.server
 from gv$session s join gv$process p on (s.inst_id = p.inst_id and s.paddr = p.addr)
 where s.type = 'USER' 
 and s.status='ACTIVE'
--and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
 --  'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS')
order by 6;



undefine spid
set linesize 300 pagesize 300
col spid for a6
col sid for 99999

col action 		for a20
col logon_time 		for a24
col module 		for a13
col cli_process 	for a7
col cli_mach 		for a25
col status 		for a10
col username 		for a10
col kill 		for a16
col PKill 		for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,p.spid,s.sql_id, PREV_SQL_ID,s.status, s.username, s.action,to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,s.module,s.last_call_et/3600,s.process cli_process,s.machine cli_mach,s.type,'kill -9 '||spid PKill
from gv$session s, gv$process p
where p.addr=s.paddr 
and  s.type != 'BACKGROUND' 
-- and p.spid=
--and s.process = 'cli_process'
-- s.sid=
;
====


-- Cpu Info 

col kill for a15
col CLIENT_INFO for a20
col USERNAME  for a20 
select * from
(
select username,''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,sid,sql_id,prev_sql_id,
       round((cpu_usage/(
                        select sum(value) total_cpu_usage
                          from gv$sesstat t
                         inner join gv$session  s on ( t.sid = s.sid )
                         inner join gv$statname n on ( t.statistic# = n.statistic# )
                         where n.name like '%CPU used by this session%'
                           and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
                        ))*100,2) cpu_usage_per_cent,
       module_info,client_info 
  from
(
select nvl(s.username,'Oracle Internal Proc.') username,s.sid,s.serial#,s.inst_id,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info,
sql_id,prev_sql_id
  from gv$sesstat t
       inner join gv$session  s on ( t.sid = s.sid )
       inner join gv$statname n on ( t.statistic# = n.statistic# )
 where n.name like '%CPU used by this session%'
   and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
) s1
)
order by cpu_usage_per_cent desc;

===


set linesize 600 pagesize 300 
 col kill for a15
 col program for a15
 col client_identifier for a15
 col MACHINE for a15
 col ACTION    for a10                                                        
 col EVENT for a15
 col WAIT_CLASS for a12
 col MODULE for a12
 col USERNAME for a12
 select /*+ parallel(4) */ s.username,
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
 s.status,
 (select value from gv$sesstat natural join v$statname where name = 'physical reads' and sid=s.sid and inst_id=s.inst_id) disk_reads,
 (select value from gv$sesstat natural join v$statname where name = 'session logical reads' and sid=s.sid and inst_id=s.inst_id ) logical_reads,
(select value/100 cpu_sec from gv$sesstat natural join v$statname where name = 'CPU used by this session' and sid=s.sid and inst_id=s.inst_id)cpu_sec,
 s.logon_time,
 --s.program,
 --s.osuser,
 --s.machine,
 p.pid,
 p.spid,
 --s.client_identifier,
 --s.client_info,
 s.module,
 s.action,
 s.event,
 s.wait_class,
 case
 when s.status = 'ACTIVE' then
 s.last_call_et
 else
 null
 end wait_sec,
 s.blocking_session_status,
 s.blocking_instance,
 s.blocking_session,
 s.sql_id,
 s.sql_hash_value,
 s.sql_address,
 s.prev_sql_id,
 s.prev_hash_value,
 s.prev_sql_addr,
 s.sql_child_number,
 s.prev_child_number,
 s.server
 from gv$session s join gv$process p on (s.inst_id = p.inst_id and s.paddr = p.addr)
 where s.type = 'USER' 
 and s.status='ACTIVE'
 order by disk_reads desc
 
====


set lines 300 pages 2000
--col "SID,SERIAL@INST_ID" for a20
col name format a26
col username format a45
col program format a40
col SESS_CPU_SECS wra for 99999999999
col LAST_CPU_SECS wra for 99999999999
col LOGON_MIN wra for 999999999
col "CPU USAGE %" wra for 999.99
col service_name for a20
col kill for a17
SELECT * FROM (select sess_cpu.spid,
--sess_cpu.sid||','||sess_cpu.serial#||'@'||call_cpu.inst_id as "SID,SERIAL@INST_ID",
''''||sess_cpu.sid ||','|| sess_cpu.serial#||',@'||call_cpu.inst_id ||''''  as kill,
 NVL(sess_cpu.username, program) username,sess_cpu.service_name,
sess_cpu.status,sess_cpu.LAST_CALL_ET, to_char(sess_cpu.logon_time,'dd/mm/yyyy hh24:mi:ss') "LOGON TIME",
round((sysdate - sess_cpu.logon_time)*1440*60/60) LOGON_MIN,
sess_cpu.value/100 SESS_CPU_SECS, (sess_cpu.value - call_cpu.value)/100 LAST_CPU_SECS,
round ((sess_cpu.value/100)/round((sysdate - sess_cpu.logon_time)*1440*60)*100,2) "CPU USAGE %",
 --round ((sess_cpu.value/100)/round((sysdate - sess_cpu.logon_time)*1440*60)*100,2) Percent,
sess_cpu.sql_id 
from
(select se.inst_id,se.sql_id ,ss.statistic#,pr.spid,se.sid,se.serial#, se.username,se.service_name,se.status,se.last_call_et, se.program, se.logon_time, sn.name, ss.value 
from gv$session se,gv$process pr, gv$sesstat ss,v$statname sn
where se.sid=ss.sid
and se.inst_id=ss.inst_id
and pr.inst_id=se.inst_id
and sn.statistic#=ss.statistic#
and pr.addr=se.paddr
and sn.name in ('CPU used by this session') ) sess_cpu,
(select se.inst_id,ss.statistic#,se.sid, ss.value, value/100 seconds from gv$session se, gv$sesstat ss, v$statname sn
where se.sid=ss.sid
and se.inst_id=ss.inst_id
and sn.statistic#=ss.statistic#
and sn.name in ('CPU used when call started') ) call_cpu
where sess_cpu.sid=call_cpu.sid
and sess_cpu.inst_id=call_cpu.inst_id
--and sess_cpu.status = 'ACTIVE'
--order by "CPU USAGE %" DESC
) WHERE ROWNUM <=30
;





SET PAGESIZE 60
SET LINESIZE 300

COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000

SELECT
s.username,
t.sid,
s.serial#,
SUM(VALUE/100) as "cpu usage (seconds)"
FROM
v$session s,
v$sesstat t,
v$statname n
WHERE 1=1
and t.STATISTIC# = n.STATISTIC#
AND NAME like '%CPU used by this session%'
AND t.SID = s.SID
AND s.status='ACTIVE'
AND s.username is not null
GROUP BY username,t.sid,s.serial#
/

Show Memory  Usage for Active Sessions

col session for a70

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||' - '||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE",
 ssn.sql_id
,ssn.PREV_SQL_ID
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);




col BEGIN_INTERVAL_TIME for a22
col idle for 999
col sys for 999
col user1 for 999
col nice for 999
set lin 1000 pages 1000
col db_name for a35
col cpus1 for 9999
select (select instance_name||'@'||substr(host_name,-(length(host_name))) from v$instance)db_name ,cpus1,sess,date1, idle,sys,user1,nice,sql.cnt ,sql.CPU,sql.ELAPSED,sql.iowait,sql.exec
from
(
select count(1)sess,os.snap_ID ,cpus1, to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice
 from
(
select
SNAP_ID,
 cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
 round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
 round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
 round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
from
(SELECT SNAP_ID,
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
sum(decode(stat_name,'USER_TIME',value,0))   user1,
sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
sum(decode(stat_name,'NICE_TIME',value,0))   nice1
FROM   DBA_HIST_OSSTAT group by SNAP_ID))os
     , DBA_HIST_snapshot sn
     , (select * from dba_hist_active_sess_history where user_id in (select user_id from dba_users where username in ('SYS','SYSTEMS'))) ash  
where os.SNAP_ID=sn.SNAP_ID
and sn.SNAP_ID=ash.SNAP_ID
and ash.snap_id=os.snap_id
group by os.SNAP_ID,cpus1,to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice
)aa, (select snap_id,count(1)cnt,round(sum(CPU_TIME_TOTAL/1000000),0)CPU,round(sum(ELAPSED_TIME_TOTAL/1000000),0)ELAPSED
,round(sum(IOWAIT_TOTAL/1000000),0)IOWAIT,round(sum(EXECUTIONS_TOTAL),0)exec from DBA_HIST_SQLSTAT group by snap_id) sql
where sql.snap_id=aa.snap_id order by 3;

===
--memory allocations
set linesize 200 pagesize 300
column username format a20
column module format a20
SELECT a.inst_id,
       NVL(a.username,'(oracle)') AS username,
       a.module,
       a.program,
       Trunc(b.value/1024) AS memory_kb
FROM   gv$session a,
       gv$sesstat b,
       gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC;

===

-- IO etc 
 
 
  set linesize 300 pagesize 300
  col kill for a17
  col event for a20
  select   ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
    s.status, 
                  -- s.inst_id inst,
                   spid, 
                  -- s.serial#, 
   s.sql_id,
                   to_char(s.logon_time,'DD.MM HH24:MI') logon, 
                   s.username, s.osuser,
                   substr(s.program,1,24) program,
   s.event,
                   s.terminal,
                   consistent_gets cons_gets, 
                   block_gets, physical_reads ph_reads,
                   trunc(100*(consistent_gets + block_gets - physical_reads) /(0.1 + consistent_gets + block_gets),2) Hit_ratio,
                   trunc(100 * (block_changes + consistent_changes)  /(0.1 + block_gets + consistent_gets),2) Undo
           from    
               gv$session s,
               gv$sess_io i,
               gv$process p,
               gv$bgprocess b
           where
               s.sid = i.sid
               and s.inst_id = i.inst_id
               and s.paddr = p.addr
               and s.inst_id = p.inst_id
               and s.paddr = b.paddr(+)
   and STATUS!='INACTIVE'
   and s.osuser!='grid';





set linesize 700 pagesize 300
col kill for a16
col sql_text for a50 wrap
col program for a15
col hash_value for 9999999999999
col box for a25
col event for a27
col os_user for a15
col wait_class for a15
select /* +ANUJ */
  ''''||b.sid ||','|| b.serial#||',@'||b.inst_id ||''''  as kill,
     substr(a.spid,1,5) pid,
    status, 
   substr(b.machine,1,25) box,
   substr(b.username,1,10) username,
   substr(b.osuser,1,30) os_user,
   substr(sw.event,1,30) event,
   sw.wait_class,
   sw.seconds_in_wait waiting,
   b.server,
   decode(b.program,null,substr(a.program,1,30),substr(b.program,1,30)) program,
   TO_CHAR(LOGON_TIME, 'DD/MM/YYYY HH24:MI:SS' ) LOGON_TIME,
             CASE  
        WHEN LAST_CALL_ET< 60 THEN LAST_CALL_ET || ' Seconds'
        WHEN LAST_CALL_ET< 3600 THEN ROUND(LAST_CALL_ET/60) || ' Minutes'
        WHEN LAST_CALL_ET< 86400 THEN ROUND(LAST_CALL_ET/60/60,1) || ' Hour(s)'
      ELSE
        ROUND(LAST_CALL_ET/60/60/24,1) || ' Day(s)'
     END INACTIVE_ACTIVE_TIME,
   b.sql_id,
   b.prev_sql_id,
       optimizer_mode,
       hash_value,
       sql_text
   -- , 'ALTER SYSTEM DISCONNECT SESSION ' || CHR(39) || b.sid || ',' || b.serial# || ',@' || a.INST_ID || chr(39) || ' immediate;' kill_cmd
from
   gv$session b,
   gv$process a,
   gv$session_wait sw,
   gv$sqlarea sqlarea
where b.paddr = a.addr
      and b.inst_id = a.inst_id
      and type='USER'
    and sw.inst_id = b.inst_id
    and sw.sid = b.sid
    and b.sql_hash_value = sqlarea.hash_value
      and b.sql_address    = sqlarea.address
     and sw.event not in ('ges remote message','gcs remote message','rdbms ipc message',
                            'SQL*Net message from client','SQL*Net message to client',
                            'events in waitclass Other','DIAG idle wait','SQL*Net more data from client',
                            'PX Deq: Execute Reply','PX Deq: Join ACK','PX Deq: Parse Reply',
'Streams AQ: waiting for messages in the queue','wait for unread message on broadcast channel',
'jobq slave wait','SQL*Net message from dblink','PX Deq: Execution Msg')
and TYPE != 'BACKGROUND'
and sql_text not like '%+ANUJ%'
order by status, spid;





====

from active session 

col MODULE for a20
col ACTION for a20
select * from (
select
     '''' || SESSION_ID || ',' || session_serial# ||',@'|| inst_id||'''' kill ,
sql_id,
     program,
module,
action,
     sum(decode(session_state,'WAITING',0,1)) "CPU",
     sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "WAITING" ,
     sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "IO" ,
     sum(decode(session_state,'WAITING',1,1)) "TOTAL"
from gv$active_session_history 
where session_type='FOREGROUND'
group by '''' || SESSION_ID || ',' || session_serial# ||',@'|| inst_id||'''',sql_id,module,action,program
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum <11





set pagesize 300 linesize 300
col session_id format 999 heading "SESS|ID"
col session_serial# format 9999 heading "SESS|SER|#"
col event format a40
col total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
col kill for a16
Select 
--session_id, session_serial#, 
'''' || SESSION_ID || ',' || session_serial# ||',@'|| inst_id||'''' kill ,event, sum(time_waited) total_waits
from gv$active_session_history
where 1=1
--and session_id||session_serial# in (****)
and sample_time > sysdate -1
and program like '%rman%'
and session_state='WAITING' 
and time_waited > 0
group by '''' || SESSION_ID || ',' || session_serial# ||',@'|| inst_id||'''', Event
--Order by session_id, session_serial#, total_waits desc
;


set pagesize 300 linesize 300
col kill for a16
select * from (
select
sql_id ,
sum(decode(session_state,'on cpu',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from gv$active_session_history
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))   desc
)
where rownum <11



set pagesize 300 linesize 300
col kill for a16
select * from (
select
'''' || SESSION_ID || ',' || session_serial# ||',@'|| inst_id||'''' kill ,
sql_id ,
sum(decode(session_state,'on cpu',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from gv$active_session_history
where SQL_ID is not NULL
group by '''' || SESSION_ID || ',' || session_serial# ||',@'|| inst_id||'''',sql_id
order by sum(decode(session_state,'ON CPU',1,1))   desc
)
where rownum <11


====


-- Sql monitor 
 
col key format 9999999999999999
set colsep '|'
set linesize 400
col module format a25
col ACTION format a25
col kill for a17
select 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill ,
sm.sql_id, sm.sql_exec_id,
    to_char(max(sm.sql_exec_start) ,'dd/mm/yyyy hh24:mi:ss') as sql_exec_start,
    sm.sql_child_address child_address,
sm.plsql_object_id   ,     
sm.plsql_subprogram_id,
    sm.module,
    sm.action,
    sm.program
  from gv$sql_monitor sm, gv$session s 
  where sm.sid = s.sid
    and sm.inst_id=sm.inst_id
    and sm.session_serial# = s.serial#
and s.status = 'ACTIVE'
   group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' , sm.sql_id, sm.sql_exec_id, sm.sql_child_address,sm.plsql_object_id,sm.plsql_subprogram_id,sm.module,sm.action,sm.program
order by sql_exec_start;



set pages 500 lines 300
col opname for a10
col kill                for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.username, rj.job_name, rj.running_instance
-- , s.sid, s.serial#
, p.spid, s.lockwait, s.logon_time
from dba_scheduler_running_jobs rj,
     gv$session s,
     gv$process p
where rj.running_instance = s.inst_id
and   rj.session_id = s.sid
and   s.inst_id = p.inst_id
and   s.paddr = p.addr
-- and   p.spid = <spid>
order by s.username, rj.job_name;




SET LINES 400 PAGES 1000

CLEAR COLUMNS
CLEAR BREAKS

COMPUTE SUM LABEL 'Total in Execution' OF COUNT ON REPORT
BREAK ON REPORT

COL sql_text FOR a50

COL sql_id FOR A20
COL count  FOR 999,999
col kill for a18
col EVENT for a30
SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill,s.con_id,s.sql_id,event
  ,COUNT(*) count
  ,substr(sqla.sql_text,1,50) sql_text
FROM gv$session s, gv$sqlarea sqla
WHERE s.state = 'WAITING'
AND s.sql_id IS NOT NULL
AND sqla.sql_id=s.sql_id
AND sqla.inst_id=s.inst_id
GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' ,s.con_id ,s.sql_id,event, sqla.sql_text
ORDER BY 2
;



set lines 300 pagesize 300
col inst_sid_ser for a13
col username for a23
col serv_mod_action for a48
col tr for a2
col event for a32
col sql_id for a13
col sql_dT for 999999
col call_dT for 9999999
col W_dT for 9999
col obj# for 99999999
col kill for a17

select 	''''||sid ||','|| serial#||',@'||inst_id ||'''' kill  ,
	username||case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
	sql_id sql_id, 
	round((sysdate-sql_exec_start)*24*3600,1) sql_dT,
        last_call_et call_dT,
	case state when 'WAITING' then round(wait_time_micro/1000000,2) else round(time_since_last_wait_micro/1000000,2) end W_dT,
        decode(state,'WAITING',event,'CPU') event, 
	service_name||' '||substr(module,1,20)||' '||ACTION serv_mod_action,  
          nullif(row_wait_obj#,-1) obj#,decode(taddr,null,null,'NN') tr
from gv$session
where ((state='WAITING' and wait_class<>'Idle') or (state<>'WAITING' and status='ACTIVE'))
      --and audsid != to_number(sys_context('USERENV','SESSIONID')) -- this is clean but does not work on ADG so replaced by following line
      and (machine,port) <> (select machine,port from v$session where sid=sys_context('USERENV','SID')) --workaround for ADG
order by inst_id,sql_id
/


====


define start_date='23-06-23 08:44:00'
define end_date='23-06-23 09:44:00'


SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MM-YY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MM-YY HH24:MI') END_TIME,
         INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM GV$SYSMETRIC
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;


SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MM-YY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MM-YY HH24:MI') END_TIME,
         INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM GV$SYSMETRIC
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;


SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MM-YY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MM-YY HH24:MI') END_TIME,
         INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM GV$SYSMETRIC_SUMMARY
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;


SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a25
COLUMN snap_date   FORMAT a12
COLUMN aas         FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

WITH aas_per_hour AS (
SELECT TO_CHAR(end_time, 'dd-mm-yy') snap_date
     , metric_name
     , average
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 30
)
SELECT snap_date                                    -- the group column
     , metric_name                                  -- the series column
     , ROUND(SUM(average)/COUNT(snap_date), 2) aas  -- the value column
FROM aas_per_hour
GROUP BY snap_date
       , metric_name
ORDER BY snap_date
;

-- Average Active Sessions in Last 31 Days (interval by each hour).

SET LINESIZE 200 PAGESIZE 400

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

SELECT TO_CHAR(end_time, 'dd-mm-yy hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 30
ORDER BY snap_date_time
;

-- Average Active Sessions in Last 7 Days (interval by each day).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a25
COLUMN snap_date   FORMAT a12
COLUMN aas         FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

WITH aas_per_hour AS (
SELECT TO_CHAR(end_time, 'dd-mm-yy') snap_date
     , metric_name
     , average
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 6
)
SELECT snap_date                                    -- the group column
     , metric_name                                  -- the series column
     , ROUND(SUM(average)/COUNT(snap_date), 2) aas  -- the value column
FROM aas_per_hour
GROUP BY snap_date
       , metric_name
ORDER BY snap_date
;

-- Average Active Sessions in Last 7 Days (interval by each hour).

SET LINESIZE 200 PAGESIZE 200

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

SELECT TO_CHAR(end_time, 'dd-mm-yy hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 6
ORDER BY snap_date_time
;

-- Average Active Sessions in Last 24 Hours.

SET LINESIZE 200 PAGESIZE 400

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

SELECT TO_CHAR(end_time, 'dd-mm-yy hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 1
ORDER BY snap_date_time
;

-- Average Active Sessions in Real Time.

SET LINESIZE 200 PAGESIZE 400

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

SELECT TO_CHAR(end_time, 'dd-mm-yyyy hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(value, 2) aas                                        -- the value column
FROM v$sysmetric_history
WHERE metric_name = 'Average Active Sessions'
AND   group_id = 2                                                -- just retrieve the name with "System Metrics Long Duration" in v$metricgroup
ORDER BY snap_date_time
;

-- Average Active Sessions Custom Time Period (interval by each hour).

SET LINESIZE 200 PAGESIZE 400

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

SELECT TO_CHAR(end_time, 'dd-mm-yyyy hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   (end_time BETWEEN TO_DATE('&start_date', 'dd-mm-yy hh24:mi:ss')
                AND     TO_DATE('&end_date', 'dd-mm-yy hh24:mi:ss')
      )
ORDER BY snap_date_time
;

-- Average Active Sessions Custom Time Period (interval by each day).

SET LINESIZE 200 PAGESIZE 400

COLUMN metric_name FORMAT a25
COLUMN snap_date   FORMAT a12
COLUMN aas         FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

WITH aas_per_hour AS (
SELECT TO_CHAR(end_time, 'dd-mm-yy') snap_date
     , metric_name
     , average
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   (end_time BETWEEN TO_DATE('&start_date', 'dd-mm-yy hh24:mi:ss')
                AND     TO_DATE('&end_date', 'dd-mm-yy hh24:mi:ss')
      )
)
SELECT snap_date                                    -- the group column
     , metric_name                                  -- the series column
     , ROUND(SUM(average)/COUNT(snap_date), 2) aas  -- the value column
FROM aas_per_hour
GROUP BY snap_date
       , metric_name
ORDER BY snap_date
;

-- The original code.

SET LINESIZE 200 PAGESIZE 400

COLUMN metric_name FORMAT a25
COLUMN metric_unit FORMAT a25
COLUMN awr_aas     FORMAT 999,999.99

ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';

SELECT *
FROM (
       SELECT instance_number
            , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
            , snap_id second_snap_id
            , begin_time
            , end_time
            , metric_name
            , metric_unit
            , ROUND(average/1e2, 2) awr_aas -- metric_unit is "CentiSeconds Per Second" so average should divide by 1e2.
       FROM dba_hist_sysmetric_summary
    -- WHERE metric_name = 'DB Time Per Second' -- not "DB Time Per Second", should the following metric_name "Database Time Per Sec".
       WHERE metric_name = 'Database Time Per Sec'
       ORDER BY instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;

or

SELECT *
FROM (
       SELECT instance_number
            , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
            , snap_id second_snap_id
            , begin_time
            , end_time
            , metric_name
            , metric_unit
            , ROUND(average, 2) awr_aas
       FROM dba_hist_sysmetric_summary
       WHERE metric_name = 'Average Active Sessions'
       ORDER BY instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;


=====


spool kill.log
set head off
select 'kill -9 '|| p.spid ||'' PROCESS_ID_NEED_KILL  from v$session s, v$process p where s.paddr = p.addr and s.sql_id is null and s.event = 'latch: shared pool';
spool off
exit
begin
    for i in (select * from gv$session where  1=1
	-- and status='INACTIVE' 
	-- and (sysdate-PREV_EXEC_START)>1
	--and type != 'BACKGROUND'
	and  type = 'USER'
	)
    LOOP
        EXECUTE IMMEDIATE(q'{ALTER SYSTEM KILL SESSION '}'||i.sid||q'[,]'  ||i.serial#||q'[,@]'||i.inst_id||q'[']'||' IMMEDIATE');  
    	
		END LOOP;
		end;
		
		
		
		
		
		
		
set pagesize 0
select 'alter system kill session '''||sid||','||session_serial#||',@'||inst_id||''' immediate ;' as script from gv$sql_monitor  
where 1=1 and  status='EXECUTING' 
and module not like '%emagent%'
;

	
col "Kill" for a130
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||'''  /* '||action||' started on '||logon_time||' SQL ID '||sql_id ||'*/  ;' "Kill" 
from gv$session where module='DBMS_SCHEDULER' 
--and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'
;



set pagesize 0
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' as script from gv$session where 1=1 and  type = 'USER'
;

set pagesize 100



select count(*) from gv$session where MODULE ='DBMS_SCHEDULER'

set pagesize 0
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' as script  from gv$session where MODULE ='DBMS_SCHEDULER'
;

set pagesize 100


col kill for a70
--spool kill.sql
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' kill
--,TOTALWORK, USERNAME,SQL_ID,con_id 
from Gv$session_longops
where 1=1
and sofar < totalwork
;







*********************************************************


https://github.com/jkstill/oracle-script-lib/blob/master/sql/whog.sql


define v_10gopts = ''
var v_10gopts varchar2(10)

define v_12copts = ''
var v_12copts varchar2(10)

declare
	i_oversion integer := '&&v_oversion_major';
begin
	if i_oversion <10 then
		:v_10gopts := '--';
	else
		:v_10gopts := '';
	end if;

	if i_oversion <12 then
		:v_12copts := '--';
	else
		:v_12copts := '';
	end if;

end;
/



set  term off feed off
col v_10gopts noprint new_value v_10gopts
col v_12copts noprint new_value v_12copts
select :v_10gopts v_10gopts from dual;
select :v_12copts v_12copts from dual;
set term on feed on


col username heading 'USERNAME' format a25
col inst_id head 'INST' format 9999
col pdb head 'PDB' format a15
col sid heading 'SID' format 99999
col machine format a45 head 'MACHINE'
col osuser format a25

set recsep off term on pause off verify off echo off
set line 220  pagesize 100  trimspool on

clear break
break on username skip 1


with pdbs as (
	&&v_12copts select inst_id, con_id, name
	&&v_12copts from gv$pdbs
	&&v_12copts union all
	select rownum inst_id, 0 con_id, 'CDB' name
	from gv$instance
	union all
	select rownum inst_id, 1 con_id, 'ROOT' name
	from gv$instance
)
select distinct
	s.username,
	s.inst_id,
	&&v_12copts pdb.name pdb,
	s.machine,
	s.osuser,
	count(s.username) over 
	(
		partition by 
		s.username
		, s.inst_id
		&&v_12copts, pdb.name 
		, s.machine
		, s.osuser
	) user_count
from gv$session s
-- use outer join to show sniped sessions in
-- v$session that don't have an OS process
-- uncomment to see only your own session
left outer join gv$process p on s.inst_id = p.inst_id
	and p.addr = s.paddr
&&v_12copts join pdbs  pdb
	&&v_12copts on pdb.inst_id = s.inst_id
	&&v_12copts and pdb.con_id = s.con_id
where s.username is not null
	--and userenv('SESSIONID') = s.audsid
order by username--, sid
/


====





col username for a20 
SELECT COUNT(*),
      con_id,
       username,
       sql_id,
       type,
       server,
       status,
       state
  FROM gv$session
  where 1=1
-- and status='INACTIVE'
 GROUP BY 
       con_id,
       username,
       sql_id,
       type,
       server,
       status,
       state
 ORDER BY
       1 DESC, 2, 3, 4, 5, 6, 7;





set echo off  linesize 132 verify off feedback off serveroutput on
declare
 SID number := 0 ;
 SERIAL number := 0 ;
 username varchar(20) := '';
 Status varchar(8) := '';
 machine varchar(10) := '';
 terminal varchar(25) := '';
 program varchar(30) := '';
 Module varchar(30) := '';
 Action varchar(20) := '';
 sql_hash_value number := 0 ;
 logontime varchar(30) := '';
 last_call_et number := 0 ;
 proc number := 0 ;
 spid number := 0 ;
 event varchar(30) := '';
 state varchar(30) := '';
 sql_text varchar(1000) := '';
 
cursor cur1 is
select
 a.sid sid,
 a.serial# serial,
 a.inst_id,
 a.username username,
 a.status status ,
 a.machine machine,
 a.terminal terminal,
 a.program program,
 a.module module,
 a.action action,
 a.sql_hash_value sql_hash_value,
 to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS') logontime,
 a.last_call_et last_call_et,
 a.process proc,
 b.spid spid,
 sw.event event,
 sw.state state,
 a.sql_id
 from gv$session a, gv$process b, gv$session_wait sw
 where a.paddr=b.addr
 and a.inst_id=b.inst_id
 --and a.sid ='&sid'
 and a.inst_id=sw.inst_id
 and a.sid=sw.sid
 and a.status='ACTIVE'
 ;
begin

DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' Database session detail for the shadow process ');

DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');

 for m in cur1 loop
 DBMS_OUTPUT.ENABLE(50000);
 DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('kill............ 			: ' || ''''||m.sid ||','|| m.serial||',@'||m.inst_id ||'''' );
 --DBMS_OUTPUT.PUT_LINE('SERIAL#........ 		: ' || m.serial);
 DBMS_OUTPUT.PUT_LINE('USERNAME....... 			: ' || m.username);
 DBMS_OUTPUT.PUT_LINE('STATUS......... 			: ' || m.status);
 DBMS_OUTPUT.PUT_LINE('Machine........ 			: ' || m.machine);
 DBMS_OUTPUT.PUT_LINE('Terminal....... 			: ' || m.terminal);
 DBMS_OUTPUT.PUT_LINE('Program........ 			: ' || m.program);
 DBMS_OUTPUT.PUT_LINE('Module......... 			: ' || m.module);
 DBMS_OUTPUT.PUT_LINE('Action......... 			: ' || m.action);
 DBMS_OUTPUT.PUT_LINE('SQL Hash Value. 			: ' || m.sql_hash_value);
 DBMS_OUTPUT.PUT_LINE('Logon Time..... 			: ' || m.logontime);
 DBMS_OUTPUT.PUT_LINE('Last Call Et... 			: ' || m.last_call_et);
 DBMS_OUTPUT.PUT_LINE('sql id... 				: ' || m.sql_id);
 DBMS_OUTPUT.PUT_LINE('Process ID..... 			: ' || m.proc);
 DBMS_OUTPUT.PUT_LINE('SPID........... 			: ' || m.spid);
 DBMS_OUTPUT.PUT_LINE('Session Waiting for event:'||m.event);
 DBMS_OUTPUT.PUT_LINE('Session state ...........:'||m.state);
 for rec in ( select /*+parallel(4) */ distinct(sql_text) sql_text from gv$session s,gv$sql v 
 where s.sql_hash_value=v.hash_value and s.sql_address=v.address 
 and s.sid=m.sid
 and s.inst_id=m.inst_id
 )
 loop
 dbms_output.put_line(substr('SQL_TEXT is..........:'||rec.sql_text,1,255));
 end loop;

DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE(' ');
 end loop;
end;
/


=========================


set echo off
set linesize 132
set verify off
set feedback off
set serveroutput on;
declare
inst_id number := 0 ;
SID number := 0 ;
SERIAL number := 0 ;
username varchar(20) := '';
Status varchar(8) := '';
machine varchar(10) := '';
terminal varchar(25) := '';
program varchar(30) := '';
Module varchar(30) := '';
Action varchar(20) := '';
sql_hash_value number := 0 ;
logontime varchar(30) := '';
last_call_et number := 0 ;
proc number := 0 ;
spid number := 0 ;
event varchar(30) := '';
state varchar(30) := '';
sql_texts varchar(1000) := '';
undo_size varchar (100) := 'N/A';
cursor cur1 is
select a.inst_id, a.sid sid,
a.serial# serial,
a.username username,
a.status status ,
a.machine machine,
a.terminal terminal,
a.program program,
a.module module,
a.action action,
a.sql_hash_value sql_hash_value,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logontime,
round((a.last_call_et/60),2) last_call_et,
a.process proc,
b.spid spid,
a.event event,
a.state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
--and b.spid in ( '&spid')
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
begin
for m in cur1
loop
DBMS_OUTPUT.ENABLE(25000);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('INSTANCE ID....................... : ' || m.inst_id );
DBMS_OUTPUT.PUT_LINE('SID............................... : ' || m.sid );
DBMS_OUTPUT.PUT_LINE('SERIAL#........................... : ' || m.serial );
DBMS_OUTPUT.PUT_LINE('USERNAME.......................... : ' || m.username );
DBMS_OUTPUT.PUT_LINE('STATUS............................ : ' || m.status );
DBMS_OUTPUT.PUT_LINE('Client Machine.....................: ' || m.machine );
DBMS_OUTPUT.PUT_LINE('Terminal.......................... : ' || m.terminal);
DBMS_OUTPUT.PUT_LINE('Program........................... : ' || m.program );
DBMS_OUTPUT.PUT_LINE('Module............................ : ' || m.module );
DBMS_OUTPUT.PUT_LINE('Action............................ : ' || m.action );
DBMS_OUTPUT.PUT_LINE('SQL Hash Value.................... : ' || m.sql_hash_value );
DBMS_OUTPUT.PUT_LINE('Logon Time........................ : ' || m.logontime );
DBMS_OUTPUT.PUT_LINE('Last Call Et...................... : ' || m.last_call_et||''||'min' );
DBMS_OUTPUT.PUT_LINE('ClientPID......................... : ' || m.proc );
DBMS_OUTPUT.PUT_LINE('ServerPID......................... : ' || m.spid );
DBMS_OUTPUT.PUT_LINE('Session Waiting for Event..........: ' ||m.event );
DBMS_OUTPUT.PUT_LINE('Session state .....................: ' ||m.state);
for rec_undo in (select nvl(t.used_ublk,0)||' '||'Blocks' undo_size from gv$session s,gv$transaction t 
where
s.taddr=t.addr(+) and
s.sid=m.sid 
and s.inst_id=m.inst_id
)
loop
dbms_output.put_line('Undo Generation for sid is.........: ' ||rec_undo.undo_size);
end loop;
dbms_output.put_line('SQL_TEXT is..........:');
for rec in ( select   /* +parallel(4) */ sql_text sql_texts from gv$session s,gv$sqltext v where
s.sql_hash_value=v.hash_value and
s.inst_id=v.inst_id and
s.sql_address=v.address and s.sid=m.sid order by piece)
loop
dbms_output.put_line(' '||rec.sql_texts);
end loop;
for n in ( select /* +parallel(4) */  t.DISK_READS DISK_READS from gv$session s, gv$sqlarea t
where s.sql_hash_value =t.hash_value and s.sid=m.sid  and s.inst_id=t.inst_id)
loop
dbms_output.put_line('Disk reads due to above SQL execution ' || n.DISK_READS);
end loop;
DBMS_OUTPUT.PUT_LINE(' ' );
DBMS_OUTPUT.PUT_LINE(':------------------------------------------------: ' );
DBMS_OUTPUT.PUT_LINE(' ' );
end loop;
end;









===

ps -eo pid,vsz,rss,state,args --sort=vsz,rss| grep -i local=no |awk 'BEGIN{sum=0}{sum+=$3}END{print sum/1024/1024}'   <<<<< Huge memory consumption


ps -eo pid,vsz,rss,state,args --sort=vsz,rss| grep -i local=no |wc -l    ---session count 

select count(*)
from v$session s, v$process p
where s.paddr = p.addr
and s.username not in ('SYS','SYSTEM');

select p.spid, p.PGA_ALLOC_MEM, p.PGA_USED_MEM, s.username, s.program, s.sql_id
from v$session s, v$process p
where s.paddr = p.addr
and s.username not in ('SYS','SYSTEM')
and p.spid in();

pmap -x 1346

pmap -x 1346 | grep anon | awk 'BEGIN{sum=0}{sum+=$3}END{print sum}'


===






----  Protocol for all Oracle connections.  


col NETWORK_SERVICE_BANNER for a90
select
distinct   NETWORK_SERVICE_BANNER
from
   gv$session_connect_info
;



alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 400 pagesize 400
col kill for a17 
col machine for a38
col event for a28
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,machine
,logon_time
--,program
,event
,sql_id
,prev_sql_id,
case when program not like 'ora___@% (P%)' then
(select max(case
when NETWORK_SERVICE_BANNER like '%TCP/IP%'       	then 'TCP'
when NETWORK_SERVICE_BANNER like '%Bequeath%'       then 'BEQUEATH'
when NETWORK_SERVICE_BANNER like '%IPC%'     		then 'IPC'
when NETWORK_SERVICE_BANNER like '%SDP%'     		then 'SDP'
when NETWORK_SERVICE_BANNER like '%NAMED P%'     	then 'Named pipe'
when NETWORK_SERVICE_BANNER is null
      then 'TCPS' end)
	  from    gv$session_connect_info i
where 1=1
 and  i.sid=s.sid and i.inst_id = s.inst_id) end protocol
from
   gv$session s
   where 1=1 
   AND type != 'BACKGROUND'
  -- and  program not like '%ora%'
   order by logon_time desc 
   ;








Oracle DBA

anuj blog Archive