Di seguito alcune query per l'analisi dello spazio occupato da un DB oracle, basate sull'analisi dei datafile/tablespace e tabelle AWM.
- CRESCITA DATABASE BASATO SU DATAFILE
SELECT
(select min(creation_time) from v$datafile) ""Create Time"",
(select name from v$database) ""Database Name"",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) ""Database Size MB"",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) ""Used Space MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) ""Used in % MB"",
ROUND((FREE.P / 1024 / 1024 ),2) ""Free Space MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) ""Free in % MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) ""Growth DAY MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) ""Growth DAY in % MB"",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) ""Growth WEEK MB"",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) ""Growth WEEK in MB""
FROM (SELECT BYTES FROM gV$DATAFILE
UNION ALL
SELECT BYTES FROM gV$TEMPFILE
UNION ALL
SELECT BYTES FROM gV$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
- DATABASE CRESCITA DAY-WEEK
SELECT MIN(creation_time) Create_Time, ts.name,
ROUND(SUM(df.bytes) /1024/1024) curr_size_MB,
ROUND( (SUM(df.bytes)/1024/1024)/ROUND(sysdate-MIN(creation_time)),1) growth_per_day_MB,
ROUND( (SUM(df.bytes)/1024/1024)/ROUND(sysdate-MIN(creation_time)) * 7,1) growth_7_days_MB
FROM v$datafile df , v$tablespace ts
WHERE df.ts#=ts.ts#
GROUP BY df.ts#, ts.name
ORDER BY df.ts#
- DATABASE CRESCITA - TABLESPACE PER MESE
select v.TS#,d.tablespace_name,
v.status ""Status"" ,to_char(creation_time, 'MM-YYYY') ""Month"",
ROUND(sum(v.bytes)/1024/1024/1024,3) ""Growth in GB""
FROM v$datafile v, dba_data_files d
where creation_time >to_date('2000','YYYY')
and d.file_name = v.name
group by TS#,d.tablespace_name,
v.status,to_char(creation_time, 'MM-YYYY')
order by TS#,d.tablespace_name,
v.status,to_char(creation_time, 'MM-YYYY')
- DATABASE CRESCITA - AWR - SNAP_ID - SNAP_TIME
SELECT A.sNAP_ID,A.SNAP_TIME,
A.ProgSum/1024/1024 ProgSum,( ((c.bb-b.sad) + a.ProgSum)/(1024*1024)) MB
from
( select a.snap_id,
SNAP_TIME,
sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
from
(select SNAP_ID,
sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
from DBA_HIST_SEG_STAT
group by SNAP_ID
having sum(SPACE_ALLOCATED_TOTAL) <> 0
order by 1 ) a,
(select distinct SNAP_ID,
to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
from DBA_HIST_SNAPSHOT) b
where a.snap_id=b.snap_id ) A,
(select sum(SPACE_ALLOCATED_DELTA) sad from DBA_HIST_SEG_STAT) B,
(select sum(bytes) bb from dba_segments) C
order by 1,2
- DATABASE - MIN/MAX SPAZIO UTILIZZATO TRA 2 SNAP_ID
with snaps as (select min(snap_id) min_snap, max(snap_id) max_snap
from dba_hist_snapshot
where begin_interval_time > ADD_MONTHS(sysdate,-2))
select
O.TABLESPACE_NAME,
O.OWNER||'.'||O.OBJECT_NAME SCHEMA_OBJECT_NAME,
o.object_type,
min(SNAP_ID) MIN_OBJ_SNAP_id,
max(SNAP_ID) max_obj_snap_id,
ROUND(MIN(H.SPACE_USED_TOTAL)/1024/1024/1024,3) min_space_used_GB,
ROUND(SUM(H.SPACE_USED_DELTA)/1024/1024/1024,3) SPACE_USED_GB,
ROUND(SUM(H.SPACE_ALLOCATED_DELTA)/1024/1024/1024,3) space_alloc_GB
from
DBA_HIST_SEG_STAT H join dba_hist_seg_stat_obj o
on h.dbid=o.dbid and h.ts#=o.ts# and h.obj#=o.obj# and h.dataobj#=o.dataobj#
WHERE 1=1
AND H.SNAP_ID BETWEEN (SELECT MIN_SNAP FROM SNAPS) AND (SELECT MAX_SNAP FROM SNAPS)
and H.DBID = (select DBID from V$DATABASE)
and H.INSTANCE_NUMBER = (select INSTANCE_NUMBER from V$INSTANCE)
and O.OWNER != '** MISSING **' -- segments already gone
and O.OBJECT_NAME not like 'BIN$%' -- recycle-bin
and O.OBJECT_NAME not like 'SYS_%' -- LOBs, etc - not too representative
AND o.OWNER NOT IN ('APEX_030200','SCOTT','OWBSYS','PERFSTAT',
'FLOWS_FILES','PUBLIC','SYS','SYSTEM','OUTLN','DIP','DBSNMP','WMSYS',
'EXFSYS','DMSYS','CTXSYS','XDB','ANONYMOUS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA',
'MDSYS','OLAPSYS','MDDATA','SYSMAN','MGMT_VIEW','VIP')
group by
O.TABLESPACE_NAME,O.OWNER||'.'||O.OBJECT_NAME,o.OBJECT_TYPE
having SUM(H.SPACE_ALLOCATED_DELTA)>0
