I would like however to describe a different way using the DBA_HIST_SNAPSHOT and DBA_HIST_SEG_STAT tables.
License
First things first - is one authorized to use it? As You certainly know there is nothing sure about the Oracle licensing or at least such is the common viewpoint.I found in this regards the following excerpt (the source), but it can not be used as an excuse:
All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.Anyway we have got such license for our environments for other reasons, so do not care much for it.
The DBA_HIST_SNAPSHOT
The most important columns for me are:- DBID, SNAP_ID, INSTANCE_NUMBER - those 3 I enlist at once because they construct the only index created on underlying table WRM$SNAPSHOT (the order important). Of course the column 3 is important only if one uses RAC and the column 1 only if the data are gathered in one place from many databases. In my simple scenario I need to provide despite the SNAP_ID only the DBID (select dbid from v$database) in order to allow the optimizer to use that index if reasonable.
- STARTUP_TIME - this is a timestamp of the current database startup, very important border - all the TOTAL* values are cumulated since this timestamp. One example here: the SPACE_USED_TOTAL column not necessarily will show You the current storage space used by the segment. In fact it will show You the sum of all the deltas (in this case the SPACE_USED_DELTA column) since the STARTUP_TIME.
- BEGIN_INTERVAL_TIME, END_INTERVAL_TIME - the ceasuras placing a snapshot in time. I use mostly the BEGIN_INTERVAL_TIME as means to choose only one month worth snapshots.
The DBA_HIST_SEG_STAT
The most important columns here are:- DBID, SNAP_ID, INSTANCE_NUMBER, TS#, OBJ#, DATAOBJ# - again I list them as one. Again those columns form the only index on the underlying table WRH$_SEG_STAT. Despite that SNAP_ID provides the link to the DBA_HIST_SNAPSHOT, while TS# identifies the tablespace and the OBJ# and DATAOBJ# a particular segment
- SPACE_USED_TOTAL, SPACE_USED_DELTA, SPACE_ALLOCATED_TOTAL, SPACE_ALLOCATED_DELTA - DELTA shows the increment in used space for the current snapshot, TOTAL - aggregate of DELTAs (in this case the sum) since the startup.
- there are plenty of other counters, but they are irrelevant in the context of storage space, so I do not write about them
Finally the SQL
I will present here few possibilities. Here the first one:
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,-1))
select
O.TABLESPACE_NAME,
O.OWNER||'.'||O.OBJECT_NAME oid,
o.object_type,
--min(SNAP_ID) MIN_OBJ_SNAP,
--max(SNAP_ID) max_obj_snap,
--MIN(H.SPACE_USED_TOTAL) min_space_used,
SUM(H.SPACE_USED_DELTA) SPACE_USED,
SUM(H.SPACE_ALLOCATED_DELTA) space_alloc
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
