Hello,
I found this query in my inbox from a few years ago and apparently it returns the table size changes during given period of time.
But, when I execute it I get illogical results, such as 'Space used delta (GB)' results 80 GB for a single day, which does not make any sense.
I'm aware that restarting the DB might affect the values returned, but the DB was not restarted in the last few months.
Please advise if the query even returns the results I look? as I wish to see aggregated changes in all database tables in my scheme during a given period of time.
If possible, I even would like to exclude statistics tables.
Running on Oracle 12c and Oracle 11.2.4g.
SELECT
*
FROM
(
SELECT
TO_CHAR (end_interval_time, 'DD / MM / YY') mydate,
ROUND (SUM (space_used_delta) / 1024 / 1024 / 1024 ,2) "Space used delta (GB)",
ROUND (AVG (c.bytes) / 1024 / 1024 / 1024 ,2) "Total Object Size (GB)",
ROUND (SUM (space_used_delta) / SUM (c.bytes) * 100, 2) "Percent of Total Disk Usage (Delta)"
FROM
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
WHERE
begin_interval_time > TRUNC (SYSDATE) - 7
AND sn.snap_id = a.snap_id
AND b.object_id = a.obj#
AND b.owner = c.owner
AND b.owner = 'DBUSER'
AND b.object_name = c.segment_name
GROUP BY
TO_CHAR (end_interval_time, 'DD / MM / YY'))
ORDER BY
to_date (mydate, 'DD / MM / YY') ;