-- 
-- Create User ssdba have its own tablespace
-- connect ssdba/ssdba
-- Surender Sara
-- 
-- RUN AS sys user
-- 

drop trigger sys.ssdba_logoff_trigger ;

drop table ssdba.ssdba_logoff_event_t; 
create table ssdba.ssdba_logoff_event_t as 
select
 a.inst_id          ,
 a.SID              ,                                      
 a.EVENT            ,                                     
 a.TOTAL_WAITS      ,                                    
 a.TOTAL_TIMEOUTS           ,                                   
 a.TIME_WAITED              ,                                  
 a.AVERAGE_WAIT             ,                                 
 a.MAX_WAIT                 ,                                
 sysdate as logoff_time	    ,
 b.SERIAL#                  ,                                     
 b.AUDSID            ,
 b.PADDR             ,                                     
 b.USERNAME          ,                                     
 b.OSUSER            ,                                     
 b.PROCESS           ,                                     
 b.MACHINE           ,                                     
 b.TERMINAL          ,                                     
 b.TYPE              ,                                     
 b.logon_time        ,
 b.module            ,
 b.action            ,
 b.RESOURCE_CONSUMER_GROUP
from 
gv$session b ,
gv$session_event a
where 
a.inst_id = b.inst_id and
1=2;    

drop table ssdba.ssdba_logoff_stats_t		; 
create table ssdba.ssdba_logoff_stats_t as 
select 
a.inst_id,
c.username ,
c.osuser,
c.sid,
c.serial#,
c.AUDSID            ,
c.paddr,
c.process,
c.logon_time ,
a.statistic# ,
b.name,
a.value,
sysdate as logoff_time ,
c.module,
c.action ,
c.machine ,
c.RESOURCE_CONSUMER_GROUP
from gv$session c, gv$sesstat a, gv$statname b
where 
1=2 and
c.inst_id = a.inst_id and 
a.inst_id = b.inst_id ;


create or replace trigger sys.ssdba_logoff_trigger			
BEFORE LOGOFF ON DATABASE	
declare			
logoff_sid pls_integer;
logoff_time date :=sysdate;
begin

select sid into logoff_sid from v$mystat where rownum < 2 ;

insert into ssdba.ssdba_logoff_event_t
select
a.inst_id,
 a.SID              		,                                      
 a.EVENT            		,                                     
 a.TOTAL_WAITS      		,                                    
 a.TOTAL_TIMEOUTS   		,  	                                 
 a.TIME_WAITED      		,                                  
 a.AVERAGE_WAIT     		,                                 
 a.MAX_WAIT         		,                                
 sysdate as logoff_time	    	,
 b.SERIAL#           		,                                     
 b.AUDSID            ,
 b.PADDR             		,                                     
 b.USERNAME          		,                                     
 b.OSUSER            		,                                     
 b.PROCESS           		,                                     
 b.MACHINE           		,                                     
 b.TERMINAL          		,                                     
 b.TYPE              		,                                     
 b.logon_time 			,
 b.module				,
 b.action            		,
 b.RESOURCE_CONSUMER_GROUP
from 
gv$session b ,
gv$session_event a
where 
a.sid      = logoff_sid and
a.sid      = b.sid and
b.username = login_user and
b.sid= logoff_sid and
a.inst_id = b.inst_id
;    


insert into ssdba.ssdba_logoff_stats_t
select 
a.inst_id,
c.username 		,
c.osuser		,
a.sid			,
c.serial#		,
c.AUDSID            ,
c.paddr		,
c.process		,
c.logon_time 	,
a.statistic# 	,
b.name		,
a.value		,
logoff_time 	,
c.module		,
c.action 		,
c.machine         ,
c.RESOURCE_CONSUMER_GROUP
from 
gv$session c, 
gv$sesstat a, 
gv$statname b
where
c.inst_id = a.inst_id and 
a.inst_id = b.inst_id and
a.statistic# = b.statistic#
and a.sid=logoff_sid
and c.sid= logoff_sid
and c.username = login_user 
and b.name in
(
        'physical reads',
        'physical writes',
        'physical writes direct',
        'physical reads direct',
        'physical writes direct (lob)',
        'physical reads direct (lob)',
        'db block gets',
        'db block changes',
        'consistent changes',
        'consistent gets',
        'session pga memory',
        'session uga memory',
        'parse count (total)',
        'CPU used by this session',
        'parse time cpu',
        'recursive cpu usage',
        'sorts (disk)',
        'sorts (memory)',
        'sorts (rows)',
        'user commits',
        'user rollbacks',
        'execute count',
	 'redo entries',
	 'redo size',
	 'redo log space requests',
	 'DBWR undo block writes',
	 'transaction tables consistent reads - undo records applied',
	 'data blocks consistent reads - undo records applied',
	 'rollback changes - undo records applied'
) 
;
end;
/