ORA-01940: cannot drop a user that is currently connected ..

SYS@mactlm1> drop user anuj cascade ;
drop user anuj cascade ;
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


find and kill session ...


set linesize 200 pagesize 200
col kill for a70
select username ,'alter system kill Session ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' kill from gv$session s
where username = '&USERNAME' ;

Now drop the user 

drop user anuj cascade;

rman create set newname script 


Oracle rename datafile script for all the datafile  ..


SET NEWNAME in oracle 11g release 2.

%b  Specifies the file name stripped of directory paths. For example, if a datafile is named
    /prod/financial.dbf, then %b results in financial.dbf.
%f  Specifies the absolute file number of the datafile for which the new name is generated. For     
    example, if datafile 2 is duplicated, then %f generates the value 2.
%I  Specifies the DBID.
%N  Specifies the tablespace name.
%U  Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f.

set linesize 200
col file_name format a100
select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||''' ;' file_name from v$datafile;

 FILE_NAME
----------------------------------------------------------------------------------------------------
set newname for datafile 1 to '/oradata/dbase/system01' ;
set newname for datafile 3 to '/oradata/dbase/sysaux01' ;
set newname for datafile 4 to '/oradata/dbase/undotbs01' ;
set newname for datafile 5 to '/oradata/dbase/undotbs02' ;
set newname for datafile 6 to '/oradata/dbase/users01' ;


http://anuj-singh.blogspot.com/2021/10/rman-restore-database.html

Change this script based on your reqiurement 

run this script after mount the database 

set linesize 200 heading off feedback off 
col file_name format a100
select 'run{' from dual 
union all
select 'set newname for datafile ' ||FILE#|| ' to '||'''/xxxxxxx/xxxxxxx/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||''' ;' file_name from v$datafile
union all
select 'set until scn xxxxx;' from dual 
union all
select 'restore database;' from dual 
union all
select 'switch datafile all;' from dual 
union all
select 'recover database;' from dual 
union all
select '} ' from dual ;
 

Output ......
 
run{
set newname for datafile 1 to '/xxxxxxx/xxxxxxx/system01' ;
set newname for datafile 3 to '/xxxxxxx/xxxxxxx/sysaux01' ;
set newname for datafile 4 to '/xxxxxxx/xxxxxxx/undotbs01' ;
set newname for datafile 5 to '/xxxxxxx/xxxxxxx/undotbs02' ;
set newname for datafile 6 to '/xxxxxxx/xxxxxxx/users01' ;
set until scn xxxxx;
restore database;
switch datafile all;
recover database;
}


 
 
Open the Database resetlogs option.

RMAN> alter database open resetlogs;
 

=====

for PDB

set linesize 200
col NAME for a15
col FILE_NAME1 for a100
col file_name format a100
select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'||p.name||nvl2(p.name,'/','')|| substr(d.name,instr(d.name,'/',-1)+1, instr(substr(d.name,instr(d.name,'/',-1)+1),'.')-1 ) ||''' ;' file_name1
from v$datafile d,v$pdbs p
where 1=1
and d.CON_ID=p.CON_ID(+)
-- order by p.name
order by FILE#;


FILE_NAME1
----------------------------------------------------------------------------------------------------
set newname for datafile 1 to '/oradata/dbase/system01' ;
set newname for datafile 3 to '/oradata/dbase/sysaux01' ;
set newname for datafile 4 to '/oradata/dbase/undotbs01' ;
set newname for datafile 5 to '/oradata/dbase/PDB9/system01' ;
set newname for datafile 6 to '/oradata/dbase/PDB9/sysaux01' ;
set newname for datafile 7 to '/oradata/dbase/users01' ;
set newname for datafile 8 to '/oradata/dbase/PDB9/undotbs01' ;
set newname for datafile 9 to '/oradata/dbase/PDB9/users01' ;
set newname for datafile 10 to '/oradata/dbase/PDB9/TB_CUST' ;
set newname for datafile 11 to '/oradata/dbase/PDB9/TTS_OBJECTS' ;
set newname for datafile 13 to '/oradata/dbase/undotbs2' ;
set newname for datafile 14 to '/oradata/dbase/PDB$SEED/system01' ;
set newname for datafile 15 to '/oradata/dbase/PDB$SEED/sysaux01' ;
set newname for datafile 16 to '/oradata/dbase/PDB$SEED/undotbs01' ;
set newname for datafile 17 to '/oradata/dbase/PDB$SEED/users01' ;
set newname for datafile 18 to '/oradata/dbase/PDB$SEED/TB_CUST' ;
set newname for datafile 19 to '/oradata/dbase/PDB$SEED/TTS_OBJECTS' ;
set newname for datafile 20 to '/oradata/dbase/PDB$SEED/pdb9_undotbs' ;
set newname for datafile 21 to '/oradata/dbase/PDB9/pdb9_undotbs1' ;

19 rows selected.

 ====

-- with output 
set linesize 200
col file_name format a100
select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 )|| FILE# ||'.dbf'' ;' file_name from v$datafile;


set newname for datafile 1 to '/oradata/dbase/system011.dbf' ;
set newname for datafile 2 to '/oradata/dbase/apex_data012.dbf' ;
set newname for datafile 3 to '/oradata/dbase/sysaux013.dbf' ;
set newname for datafile 4 to '/oradata/dbase/undotbs014.dbf' ;
set newname for datafile 5 to '/oradata/dbase/undotbs025.dbf' ;
set newname for datafile 7 to '/oradata/dbase/users017.dbf' ;
set newname for datafile 8 to '/oradata/dbase/test_data018.dbf' ;
set newname for datafile 9 to '/oradata/dbase/test_data_ind_019.dbf' ;
set newname for datafile 10 to '/oradata/dbase/lobtest10.dbf' ;
set newname for datafile 11 to '/oradata/dbase/test_uniform11.dbf' ;
set newname for datafile 12 to '/oradata/dbase/datapipe12.dbf' ;
set newname for datafile 13 to '/oradata/dbase/rmanbackup13.dbf' ;
set newname for datafile 14 to '/oradata/dbase/crmi_v1_medium_table14.dbf' ;
set newname for datafile 15 to '/oradata/dbase/test_data15.dbf' ;
set newname for datafile 16 to '/oradata/dbase/audit_tbl_0116.dbf' ;
set newname for datafile 17 to '/oradata/dbase/undo_small17.dbf' ;
set newname for datafile 18 to '/oradata/dbase/test_data18.dbf' ;
set newname for datafile 19 to '/oradata/dbase/test_data19.dbf' ;
set newname for datafile 20 to '/oradata/dbase/test_data20.dbf' ;
set newname for datafile 21 to '/oradata/dbase/test_lob21.dbf' ;
set newname for datafile 22 to '/oradata/dbase/test_data22.dbf' ;
set newname for datafile 23 to '/oradata/dbase/test_data23.dbf' ;
set newname for datafile 24 to '/oradata/dbase/test_data24.dbf' ;
set newname for datafile 25 to '/oradata/dbase/rec_catalog25.dbf' ;

24 rows selected.

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

 datafile no at the end ... 

set linesize 200 

heading off feedback off 

col file_name format a150

col "Query For RMAN Restoration" for a100

select 'run {' "Query For RMAN Restoration" from dual 

union all

select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 )|| FILE# ||'.dbf'' ;' file_name from v$datafile

union all

select 'restore database;' from dual 

union all

select 'switch datafile all;' from dual 

union all

select 'recover database;' from dual 

union all

select '} ' from dual ;

---

for pdbs file 

set linesize 200 

heading off feedback off 

col file_name format a150

col "Query For RMAN Restoration" for a100

select 'run {' "Query For RMAN Restoration" from dual 

union all

-- select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 )|| FILE# ||'.dbf'' ;' file_name from v$datafile

select 'set newname for datafile ' ||FILE#|| ' to '||'''/oradata/dbase/'||p.name||nvl2(p.name,'/','')|| substr(d.name,instr(d.name,'/',-1)+1, instr(substr(d.name,instr(d.name,'/',-1)+1),'.')-1 ) || FILE# ||'.dbf'' ;' file_name 

from v$datafile d,v$pdbs p

where 1=1

and d.CON_ID=p.CON_ID(+)

union all

select 'restore database;' from dual 

union all

select 'switch datafile all;' from dual 

union all

select 'recover database;' from dual 

union all

select '} ' from dual 

--order by 1

;

 datafile no at the end ... 

Query For RMAN Restoration

----------------------------------------------------------------------------------------------------

run {

set newname for datafile 7 to '/oradata/dbase/users7.dbf' ;

set newname for datafile 1 to '/oradata/dbase/system1.dbf' ;

set newname for datafile 3 to '/oradata/dbase/sysaux3.dbf' ;

====

simple with file no 

set pagesize 100

define new_loc='/dumps/'

select  'set newname for datafile ' || x.FILE# || ' to ' ||  

       new_file_Location ||  substr(x.NAME,instr(x.name,'/',-1)+1 ,length(x.name)-instr(x.name,'/',-1))||'_'||x.FILE#  ||'.dbf'

          from v$datafile x,(select '&new_loc' new_file_Location from dual);

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 150 pages 0
select
'kill .................................................: '||''''||x.sid ||','|| x.serial#||',@'||x.inst_id ||'''',
--'SID ................................................: '||x.sid,
--'Serial .............................................: '||x.serial#,
'con_id .............................................: '||x.con_id,
'Username ...........................................: '||x.username,
'SQLID ..............................................: '||x.sql_id,
'PHV ................................................: '||plan_hash_value,
'DISK_READS .........................................: '||st.DISK_READS,
'BUFFER_GETS ........................................: '||st.BUFFER_GETS,
'ROWS_PROCESSED ..... ...............................: '||st.ROWS_PROCESSED,
'Event  .............................................: '||x.event,
'OSUser .............................................: '||x.osuser,
'Status .............................................: '||x.status,
'BLOCKING_SESSION_STATUS ............................: '||x.BLOCKING_SESSION_STATUS,
'BLOCKING_INSTANCE ..................................: '||x.BLOCKING_INSTANCE,
'BLOCKING_SESSION ...................................: '||x.BLOCKING_SESSION,
'PROCESS ............................................: '||x.process,
'MACHINE ............................................: '||x.machine,
'PROGRAM ............................................: '||x.program,
'MODULE .............................................: '||x.module,
'ACTION .............................................: '||x.action,
'LOGONTIME ..........................................: '||TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime,
'LAST_CALL_ET .......................................: '||x.LAST_CALL_ET,
'SECONDS_IN_WAIT ....................................: '||x.SECONDS_IN_WAIT,
'STATE ..............................................: '||x.state,
'RUNNING_SINCE ......................................: '||ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':' || ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':' || ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNNING_SINCE,
'SQLTEXT ............................................: '||sql_text
from   gv$sqlstats st
,gv$session x
-- ,gv$sqlarea st
where  1=1
and x.sql_id = st.sql_id
and x.inst_id = st.inst_id
-- and x.sql_hash_value = st.hash_value
-- and    x.sql_address    = st.address
-- and x.SQL_ADDRESS    = st.ADDRESS
-- and x.SQL_HASH_VALUE = st.HASH_VALUE
and    x.status='ACTIVE'
and    sql_text not like '%kill%'
--and x.USERNAME  Not  in  ( 'SYS ' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- and buffer_gets   > 10000000000   or disk_reads   > 10000000000   or elapsed_time > 10000000000
order by RUNNING_SINCE desc
;

set linesize 80 pages 80









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 PAGESIZE 1000

col MACHINE for a40

SELECT machine,

       NVL(active_count, 0) AS active,

       NVL(inactive_count, 0) AS inactive,

       NVL(killed_count, 0) AS killed 

FROM   (SELECT machine, status, count(*) AS quantity

        FROM   v$session

        GROUP BY machine, status)

PIVOT  (SUM(quantity) AS count FOR (status) IN ('ACTIVE' AS active, 'INACTIVE' AS inactive, 'KILLED' AS killed))

ORDER BY machine;

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
;


set linesize 300
COLUMN command           HEADING "Disconnect Command"   FORMAT a90
COLUMN command2          HEADING "Kill Command"         FORMAT a60

BEGIN
  FOR i IN (select 'alter system disconnect session '''  || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate ; '
  ||'--'||s.con_id||'..'||s.username --- comment this line if required 
  Command 
            , 'alter system kill session '''  || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate ; '       Command2
            from  gv$session s
                , gv$process p
            where s.inst_id = p.inst_id (+)
              AND   s.PADDR = p.ADDR (+)
			  and s.username not in ('SYS')
        
		  )
  LOOP
     DBMS_OUTPUT.PUT_LINE(   i.command || chr(10) || i.command2 );
  END LOOP;  
END;
/
output
alter system kill session '3343,8581,@1' immediate ;
alter system disconnect session '3410,55128,@1' immediate ; --5..ANUJ

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

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 
   ;

set serveroutput on size 100000
DECLARE
  v_sid number;
  s gv$session%ROWTYPE;
  p gv$process%ROWTYPE;
BEGIN
  for i in (select inst_id,sid,osuser,username,to_char(s.last_call_et/60/60, '9990.0')  from gv$session s 
  where 1=1
  and status='ACTIVE' 
 -- and username is not null
 and username ='XXXX'
 and sql_id='1mavr84vthn3s'
  )
loop
  select * into s from gv$session where sid  = i.sid and inst_id= i.inst_id;
  select * into p from gv$process where addr = s.paddr and inst_id= s.inst_id;
  dbms_output.put_line('=====================================================================');
  dbms_output.put_line('SID/Serial  		: alter system kill session '  || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''' immediate; ' );
 -- dbms_output.put_line('SID/Serial  		: '|| ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' );
  dbms_output.put_line('Foreground  		: '|| 'PID: '||s.process||' - '||s.program);
  dbms_output.put_line('Shadow      		: '|| 'PID: '||p.spid||' - '||p.program);
  dbms_output.put_line('Terminal    		: '|| s.terminal || '/ ' || p.terminal);
  dbms_output.put_line('OS User     		: '|| s.osuser||' on '||s.machine);
  dbms_output.put_line('Ora User    		: '|| s.username);
  dbms_output.put_line('Sql Id/PREV_SQL_ID  : '|| nvl(s.sql_id,s.PREV_SQL_ID) );
  dbms_output.put_line('Status Flags		: '|| s.status||' '||s.server||' '||s.type);
  dbms_output.put_line('Tran Active 		: '|| nvl(s.taddr, 'NONE'));
  dbms_output.put_line('Login Time  		: '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
  dbms_output.put_line('Last Call   		: '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '999990.0') || ' min');
  dbms_output.put_line('Lock/ Latch 		: '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
  dbms_output.put_line('Latch Spin  		: '|| nvl(p.latchspin, 'NONE'));
  dbms_output.put_line('Current SQL statement:');
  for c1 in ( select * from gv$sqltext
              where HASH_VALUE = s.sql_hash_value and inst_id= s.inst_id order by piece) loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
  dbms_output.put_line('Previous SQL statement:');
  for c1 in ( select * from gv$sqltext
              where HASH_VALUE = s.prev_hash_value and inst_id=s.inst_id order by piece) loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
  dbms_output.put_line('Session Waits:');
  for c1 in ( select * from gv$session_wait where sid = s.sid and inst_id=s.inst_id) loop
    dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
  end loop;
  dbms_output.put_line('Connect Info:');
  for c1 in ( select * from gv$session_connect_info where sid = s.sid and inst_id= s.inst_id) loop
    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
  end loop;
  end loop;
  dbms_output.put_line('=====================================================================');
END;
/

>=12c 

set serveroutput on
set echo off feed off veri off
exec dbms_output.enable(20000)



 
 define sid=251
 define IID=1

DECLARE
        v_sid number;
        v_inst number;

        s sys.gv$session%ROWTYPE;
        p sys.gv$process%ROWTYPE;

BEGIN
  begin
    select sid, inst_id into v_sid , v_inst
        from   sys.gv$session s
        where 1=1
		and sid   = &&SID
          and   inst_id = &&IID
		  ;
  exception
     when no_data_found then
           dbms_output.put_line('Unable to find SID &&SID!!!');
           return;
         when others then
       dbms_output.put_line(sqlerrm);
           return;
   end;

   select * into s from sys.gv$session where sid = v_sid and inst_id = v_inst;
   select * into p from sys.gv$process where addr = s.paddr and inst_id = v_inst;

   dbms_output.put_line('================================================================');
  -- dbms_output.put_line('Instance no : '|| s.inst_id);
   --dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);
   dbms_output.put_line('Kill- SID/Serial/inst  : '|| ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''');
   dbms_output.put_line('con_id  : '||s.con_id);
   dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);
   dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);
   dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);
   dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
   dbms_output.put_line('Ora User    : '|| s.username);
   dbms_output.put_line('SQL_ID      : '|| s.sql_id);
   dbms_output.put_line('p1      : '|| s.p1);
   dbms_output.put_line('p2      : '|| s.p2);
   dbms_output.put_line('p3      : '|| s.p3);
   dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
   dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
   dbms_output.put_line('Login Time  : '|| To_Char(s.logon_time, 'Dy HH24:MI:SS'));
   dbms_output.put_line('Last Call   : '|| To_Char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || To_Char(s.last_call_et/60, '9999999999.0') || ' min');
   dbms_output.put_line('Lock / Latch: '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
   dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));

   dbms_output.put_line('Current SQL Statement:');
   for c1 in ( select * from sys.gv$sqltext
               where HASH_VALUE = s.sql_hash_value and INST_ID=s.inst_id order by piece) loop
           dbms_output.put_line(chr(9)||c1.sql_text);
         end loop;

   dbms_output.put_line('Previous SQL Statement:');
   for c1 in ( select * from sys.gv$sqltext
               where HASH_VALUE = s.prev_hash_value and INST_ID=s.inst_id order by piece) loop
           dbms_output.put_line(chr(9)||c1.sql_text);
         end loop;

   dbms_output.put_line('Session Waits:');
   for c1 in ( select * from sys.gv$session_wait where sid = s.sid and INST_ID=s.inst_id) loop
       dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
     end loop;

   dbms_output.put_line('Connect Info:');
   for c1 in ( select * from sys.gv$session_connect_info where sid = s.sid and INST_ID=s.inst_id) loop
       dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
     end loop;

   dbms_output.put_line('Locks:');
   for c1 in ( select
           decode(l.type,
                   -- Long locks
                               'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
                                           'UL', 'PLS USR LOCK',
               -- Short locks
                               'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
                                           'CI', 'CROSS INST F', 'DF', 'DATA FILE   ',
                                           'CU', 'CURSOR BIND ',
                                           'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STARTUP',
                                           'DR', 'RECO LOCK   ', 'DX', 'DISTRIB TRAN ',
                                           'FS', 'FILE SET    ', 'IN', ' INSTANCE NUM',
                                           'FI', 'SGA OPN FILE',
                                           'IR', 'INSTCE RECVR', 'IS', 'GET STATE    ',
                                           'IV', 'LIBCACHE INV', 'KK', ' LOG SW KICK ',
                                           'LS', 'LOG SWITCH  ',
                                           'MM', 'MOUNT DEF   ', 'MR', 'MEDIA RECVRY ',
                                           'PF', 'PWFILE ENQ  ', 'PR', 'PROCESS STRT ',
                                           'RT', 'REDO THREAD ', 'SC', 'SCN ENQ      ',
                                           'RW', 'ROW WAIT    ',
                                           'SM', 'SMON LOCK   ', 'SN', 'SEQNO INSTCE ',
                                           'SQ', 'SEQNO ENQ   ', 'ST', 'SPACE TRANSC ',
                                           'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ  ',
                                           'TD', 'DLL ENQ     ', 'TE', 'EXTEND SEG   ',
                                           'TS','TEMP SEGMENT ', 'TT', ' TEMP SPACE  ',
                                           'UN', 'USER NAME   ', 'WL', 'WRITE REDO   ',
                                           'TYPE='||l.type) type,
                    decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                                        4, 'S', 5, 'RSX', 6, 'X',
                                                        To_Char(l.lmode) ) lmode,
            decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                                        4, 'S', 5, 'RSX', 6, 'X',
                            To_Char(l.request) ) lrequest,
            decode(l.type, 'MR', o.object_name,
                           'TD', o.object_name,
                           'TM', o.object_name,
                           'RW', 'FILE#='||substr(l.id1,1,3)||
                                 ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                            'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                            'WL', 'REDO LOG FILE#='||l.id1,
                            'RT', 'THREAD='||l.id1,
                            'TS',  decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
                            'ID1='||l.id1||' ID2='||l.id2) objname
          from sys.gv$lock l, dba_objects o
          where sid = s.sid
          and l.INST_ID=s.inst_id
          and l.id1 = o.object_id ) loop
       dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
      end loop;

    dbms_output.put_line('=================================================================================');

END;
/

undef SID
set feedback on
set feed on

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

set linesize 500
col Machine  for a20
col Module   for a20                                                        
col Action for a20

col kill for a17
col Command for a10
col CLIENT_INFO     for a10                                                 
col CLIENT_IDENTIFIER for a10
 with vs as (select rownum rnum, 
  ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,
-- inst_id,
  --   sid,
    --   serial#,
  status,
  username,
  last_call_et,
  command,
  machine,
  osuser,
  module,
  action,
  resource_consumer_group,
  client_info,
  client_identifier,
  type,
  terminal,
  sql_id,
  sql_child_number,
  event,
 p1,p2,p3
from gv$session) 
 select --vs.inst_id, vs.sid ,serial# serial,
 kill ,
 vs.sql_id, vs.sql_child_number,
        vs.username "Username",
        case when vs.status = 'ACTIVE' 
                  then last_call_et 
             else null end "Seconds in Wait",
        (select command_name from v$sqlcommand where command_type = vs.command ) "Command",
        vs.machine "Machine",
        vs.osuser "OS User", 
        lower(vs.status) "Status",
        vs.module "Module",
        vs.action "Action",
        vs.resource_consumer_group,
        vs.client_info,
        vs.client_identifier,
		event,
		 p1,p2,p3
   from vs 
  where vs.USERNAME is not null
    --and nvl(vs.osuser,'x') <> 'SYSTEM'
	 and vs.USERNAME  not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
    and vs.type <> 'BACKGROUND'
	and vs.status!='INACTIVE'
    order by 1,2,3 
	
	;
	
	

====

col spoolname new_value spoolname

select 'session_rpt_'||to_char(sysdate,'dd-mm-yyyy') spoolname from dual;

--spool 'spoolname'

prom list of sessions
Set lines 300 pages 1000
col current_time for a50
select 'Anuj' author,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_time,instance_name,version,status,instance_role from v$instance
/


select * from gv$resource_limit where RESOURCE_NAME in('processes','sessions');

col sid 			form 99999
col serial# 		form 99999
col spid 			form a6
col program heading 'program' for a25 trunc
col username 		form a15
col osuser 			form a10
col idle 			form a30 heading "Idle"
col terminal 		form a12
col logon_time 		form a18
col machine 		for a15 trunc
col rn 				for 9999
col service_name 	for a30

break on report
compute sum of cnt on report
select username,status,count(*) cnt from gv$session group by username,status order by 1
/

select username,machine,count(*) cnt from gv$session group by username,machine order by 1,2
/

select username,machine,failed_over,count(*) cnt from gv$session where failed_over='YES' 
group by username,machine,failed_over order by 1,2
/

select server,status,count(*) from gv$session group by server,status
/

select inst_id,service_name,count(*) cnt from gv$session group by inst_id,service_name order by 1,2
/

select inst_id,pname,username,count(*) cnt from gv$process group by inst_id,pname,username
/

select machine,program,count(*) from gv$session where type='USER' 
group by machine,program order by 1,2
/

select machine,server,username, count(*) cnt
          from gv$session
         -- where program like 'oracle@qdyy%(TNS V1-V3)'
         -- and machine in('xxx')
          group by machine,server,username
/


set linesize 400 pagesize 300
col USERNAME for a20 
col MACHINE  for a40
col rn for 999
col IDLE for a30
ttitle -
   center 'displays the top 50 longest idle times' skip 2
col kill for a17

select a.*
from (
  select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,
  con_id,
  username,status, to_char(logon_time,'dd-mm-yy hh:mi:ss') logon_time
    , floor(last_call_et/3600)				||' hours '
        || floor(mod(last_call_et,3600)/60)	||' mins '
        || mod(mod(last_call_et,3600),60)	||' secs' idle
    , machine ,row_number() over(order by last_call_et desc ) rn
  from gv$session
  where type='USER' ) a
where rn<= 50
/


title off

column event heading 'wait event' for a30 trunc

ttitle -
   center 'displays active session' skip 2

select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,
  con_id,
  username,event,program,MACHINE,sql_id,BLOCKING_SESSION from gv$session where status='ACTIVE' and username is not null;

title off

set linesize 700 
set kill for a15
col PROGRAM  for a20                                       
col OSUSER   for a15                    
col MACHINE  for a20
col EVENT    for a12
col CLIENT_INFO for a10 
col CLIENT_IDENTIFIER for a14
col WAIT_CLASS  for a25
col ACTION for a15
col STATUS for a8
col LOGICAL_READS for 9999999999999
col kill for a17
select 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
s.con_id,
p.pid,
p.spid,
s.username,
s.status,
(select value from gv$sesstat natural join v$statname             where name = 'physical reads' and sid=s.sid)disk_reads,
(select value from gv$sesstat natural join v$statname             where name = 'session logical reads' and sid=s.sid) 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)cpu_sec,
s.logon_time,
s.program,
s.osuser,
s.machine,
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' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON')
order by disk_reads desc
 

from web 

set serverouput  on 

SET SERVEROUT ON;
DECLARE
  l_sid_serial DBMS_UTILITY.name_array; -- e.g.: 123,90536 (associative array type)
  l_statament VARCHAR2(32767);
  session_marked_for_kill EXCEPTION;
  PRAGMA EXCEPTION_INIT(session_marked_for_kill, -00031); -- ORA-00031: session marked for kill
  session_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(session_does_not_exist, -00030); -- ORA-00030: User session ID does not exist.
BEGIN
  SELECT sid||','||serial#||',@'||inst_id
  BULK COLLECT INTO l_sid_serial
  FROM gv$session
  WHERE type = 'USER' 
AND sid <> SYS_CONTEXT('USERENV', 'SID')
;
  --
  IF l_sid_serial.LAST >= l_sid_serial.FIRST THEN -- sessions found
    SYS.DBMS_SYSTEM.KSDWRT(dest => 2, tst => TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')||' cs_kill_machine: killing '||(l_sid_serial.LAST - l_sid_serial.FIRST + 1)||'sessions'
 );
    FOR i IN l_sid_serial.FIRST .. l_sid_serial.LAST
    LOOP
      l_statament := 'ALTER SYSTEM DISCONNECT SESSION '''||l_sid_serial(i)||''' IMMEDIATE';
      DBMS_OUTPUT.put_line(l_statament||';');
      BEGIN
        EXECUTE IMMEDIATE l_statament;
      EXCEPTION
        WHEN session_marked_for_kill OR session_does_not_exist THEN NULL;
      END;
    END LOOP;
    SYS.DBMS_OUTPUT.put_line((l_sid_serial.LAST - l_sid_serial.FIRST + 1)||' sessions killed!');
  ELSE
    DBMS_OUTPUT.put_line('no sessions found');
  END IF;
END;
/

SET SERVEROUT ON

DECLARE

  l_sid_serial DBMS_UTILITY.name_array; -- e.g.: 123,90536 (associative array type)
  l_statament VARCHAR2(32767);
  session_marked_for_kill EXCEPTION;
  PRAGMA EXCEPTION_INIT(session_marked_for_kill, -00031); -- ORA-00031: session marked for kill
  session_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(session_does_not_exist, -00030); -- ORA-00030: User session ID does not exist.

BEGIN
  SELECT root_blocker.sid||','||root_blocker.serial#||',@'||root_blocker.inst_id
  BULK COLLECT INTO l_sid_serial
  FROM gv$session blockee, gv$session root_blocker
  WHERE blockee.type = 'USER' 
AND blockee.sid <> SYS_CONTEXT('USERENV', 'SID') 
AND blockee.final_blocking_session_status = 'VALID'
  AND root_blocker.sid = blockee.final_blocking_session 
AND root_blocker.type = 'USER' 
AND root_blocker.sid <> SYS_CONTEXT('USERENV', 'SID')
and blockee.inst_id=root_blocker.inst_id
;
  --
  IF l_sid_serial.LAST >= l_sid_serial.FIRST THEN -- sessions found
    SYS.DBMS_SYSTEM.KSDWRT(dest => 2, tst => TO_CHAR(SYSTIMESTAMP, 'DD-MM-YYYY"T"HH24:MI:SS.FF3')||' cs_kill_root_blockers: killing '||(l_sid_serial.LAST - l_sid_serial.FIRST + 1)||' root blocker sessions');
    FOR i IN l_sid_serial.FIRST .. l_sid_serial.LAST
    LOOP
      l_statament := 'ALTER SYSTEM DISCONNECT SESSION '''||l_sid_serial(i)||''' IMMEDIATE';
      DBMS_OUTPUT.put_line(l_statament||';');
      BEGIN
        EXECUTE IMMEDIATE l_statament;
      EXCEPTION
        WHEN session_marked_for_kill OR session_does_not_exist THEN NULL;
      END;
    END LOOP;
    SYS.DBMS_OUTPUT.put_line((l_sid_serial.LAST - l_sid_serial.FIRST + 1)||' sessions killed!');
  ELSE
    DBMS_OUTPUT.put_line('no sessions found');
  END IF;
END;
/

set linesize 300
COL sessions 	FOR 9999
COL active 	FOR 9999
COL inactive 	FOR  9999
COL killed 	FOR  9999

--
SELECT 
inst_id,
con_id,
COUNT(*) sessions,
       SUM(CASE status WHEN 'ACTIVE'   THEN 1 ELSE 0 END) 	active,
       SUM(CASE status WHEN 'INACTIVE' THEN 1 ELSE 0 END) 	inactive,
       SUM(CASE status WHEN 'KILLED'   THEN 1 ELSE 0 END) 	killed,
       machine
  FROM gv$session
 WHERE 1=1
and type = 'USER'
   AND sid <> SYS_CONTEXT('USERENV', 'SID')
 GROUP BY
inst_id,
con_id,
machine
 ORDER BY    machine
/