Analisi DB

Query per analizzare il numero delle transizioni basandoci sui TBS:

select name, phyrds "Physical Reads", phywrts  "Physical Writes", lglrds  "Logical Reads" from  
(select t.name, sum(ss.PHYSICAL_READS_DELTA) phyrds, sum(ss.PHYSICAL_WRITES_DELTA) phywrts, sum(ss.LOGICAL_READS_DELTA) lglrds 
from DBA_HIST_SEG_STAT ss join v$tablespace t on ss.ts#=t.ts#   
join DBA_HIST_SNAPSHOT s on ss.snap_id=s.snap_id where  
trunc( cast(begin_interval_time as date)) = trunc(sysdate-1) 
group by t.name);

select name, phyrds "Physical Reads", phywrts  "Physical Writes", lglrds  "Logical Reads" from  
(select t.name, sum(ss.PHYSICAL_READS_DELTA) phyrds, sum(ss.PHYSICAL_WRITES_DELTA) phywrts, sum(ss.LOGICAL_READS_DELTA) lglrds 
from DBA_HIST_SEG_STAT ss join v$tablespace t on ss.ts#=t.ts#   
join DBA_HIST_SNAPSHOT s on ss.snap_id=s.snap_id where  
trunc( cast(begin_interval_time as date)) = trunc(sysdate-1)  
group by t.name) where not regexp_like(NAME,'^SYSTEM|^TEMP|^SYSAUX') ORDER BY NAME;

Lista utenti e relativo TBS di default:

select USERNAME,DEFAULT_TABLESPACE from DBA_USERS WHERE DEFAULT_TABLESPACE NOT LIKE 'USERS' AND DEFAULT_TABLESPACE NOT LIKE 'SYSTEM' AND DEFAULT_TABLESPACE NOT LIKE 'SYSAUX';