/*************************************************/
/******* For how long the statements are running *******/
/*************************************************/

select sid,username, decode(status,'ACTIVE',round(last_call_et/60),0) time,
resource_consumer_group,status,server,lockwait,
osuser,process,program,module,action, machine from V$SESSION
where username is not null
order by 3 desc

/*************************************************/
/******* CPU used by every session *******************/
/*************************************************/

SELECT sess.username, v.sid, substr(s.name,1,30) "Statistic", v.value
FROM v$statname s , v$sesstat v , v$session sess
WHERE s.name = 'CPU used by this session'
and v.statistic#=s.statistic#
and v.value > 0
and sess.sid = v.sid
ORDER BY 4 desc

/*************************************************/
/******* Memory used ******************************/
/*************************************************/

SELECT NVL(a.username,'{Background Task}') "Username",
a.program "Program",
Trunc(b.value/1024) "Memory (Kb)"
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC

/*************************************************/
/******* Datafile I/O *******************************/
/*************************************************/

select name df,
phywrts writes,
phyrds reads
from v$datafile a,
v$filestat b
where a.file# = b.file#
order by 2 desc ,1 desc

/*************************************************/
/******* DBA users in database **********************/
/*************************************************/

select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_role_privs
where grantee != 'SYS'
and granted_role = 'DBA'

/*************************************************/
/******* What rollback segment is used ****************/
/*************************************************/

select a.username, a.sid, a.taddr, b.XIDUSN as "RSEG_ID", c.segment_name, d.sql_text
from
v$session a,
v$transaction b,
dba_rollback_segs c,
v$sql d
where
a.taddr = b.addr
and a.sql_address = d.address
and b.xidusn = c.segment_id

/*************************************************/
/******* Locks ************************************/
/*************************************************/

select * from dba_waiters where holding_session
not in (select waiting_session from dba_waiters)

/*************************************************/
/******* Consumer groups **************************/
/*************************************************/

Select
V.NAME,
V.ACTIVE_SESSIONS, V.REQUESTS, V.CPU_WAIT_TIME, V.CPU_WAITS,
V.CONSUMED_CPU_TIME, V.CURRENT_UNDO_CONSUMPTION
From SYS.V_$RSRC_CONSUMER_GROUP V

/*************************************************/
/******* Rollback contention *************************/
/*************************************************/

select 'The average of waits/gets is '||
round((sum(waits) / sum(gets)) * 100,2)||'%'
From v$rollstat

/*************************************************/
/******* Session info *******************************/
/*************************************************/

select n.name,s.value
from v$statname n,V$sesstat s
where n.statistic# = s.statistic#
and value > 0
and s.sid = (select a.sid from v$process p,v$session a
where p.addr =a.paddr
and a.audsid = userenv('sessionid'))
order by n.class,n.name

/*************************************************/
/******* Log switch *********************************/
/*************************************************/

SELECT to_char(first_time, 'mm/dd') "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),'999')) "00",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),'999')) "01",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),'999')) "02",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),'999')) "03",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),'999')) "04",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),'999')) "05",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),'999')) "06",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),'999')) "07",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),'999')) "08",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),'999')) "09",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),'999')) "10",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),'999')) "11",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),'999')) "12",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),'999')) "13",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),'999')) "14",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),'999')) "15",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),'999')) "16",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),'999')) "17",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),'999')) "18",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),'999')) "19",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),'999')) "20",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),'999')) "21",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),'999')) "22",
decode(to_char(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),'999'),' 0',' ',to_char(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),'999')) "23"
FROM V$log_history
group by to_char(first_time, 'mm/dd'), to_char(first_time, 'Dy')
order by 1 desc